How do I create a formula to remove dashes?
in [General]
|
Prev: Count number times text appears in string
Next: Problem with Paste and Automatically repeating values - no fill ha
From: jackierae17 on 5 May 2010 09:46 I'm new to Excel. I have a huge inventory spreadsheet where I need to change a column to reflect the following. "P01-02-C-1" should become "P102C1" (remove 0 and dashes) "V1-25-F-2E" should become "125F2E" (remove V and dashes) HELP!!
From: Don Guillett on 5 May 2010 09:59 highlight the column header (ie C)>edit>replace> - >> blank>>>> -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "jackierae17" <jackierae17(a)discussions.microsoft.com> wrote in message news:CC3C8AF8-DBEC-444A-99AE-2AE62CBCD181(a)microsoft.com... > I'm new to Excel. I have a huge inventory spreadsheet where I need to > change > a column to reflect the following. > > "P01-02-C-1" should become "P102C1" (remove 0 and dashes) > > "V1-25-F-2E" should become "125F2E" (remove V and dashes) > > HELP!!
From: Jacob Skaria on 5 May 2010 10:02 Try =SUBSTITUTE(SUBSTITUTE(A1,"-",),"0",) Removing 0 and dashes will make that "P12C1" and not "P102C1" =SUBSTITUTE(SUBSTITUTE(A1,"V",),"-",) -- Jacob (MVP - Excel) "jackierae17" wrote: > I'm new to Excel. I have a huge inventory spreadsheet where I need to change > a column to reflect the following. > > "P01-02-C-1" should become "P102C1" (remove 0 and dashes) > > "V1-25-F-2E" should become "125F2E" (remove V and dashes) > > HELP!!
From: Roxana on 5 May 2010 10:05 I have the perfect solution for you and it involves two easy steps: (1) You will use the TEXT TO COLUMNS function to separate each set as : V1 in one column, 25 in another column, F in another column, 2E in another column, etc. (Data: Text to Columns> click on "Delimited">select a delimiter: "Other" and type a dash in it (-); click NEXT; click FINISH (this will separate or parse your data). (2) Now you want all of these pieces in one column, so use this function: ampersand. What you do is, go to the cell where you want your set of data, type "=", click on the cell where you have the "V1" and type "&" in the formula, now click on the cell where you have "25 and type "&", and so on forth until you get your final product: "V125F2E". Roxana "jackierae17" wrote: > I'm new to Excel. I have a huge inventory spreadsheet where I need to change > a column to reflect the following. > > "P01-02-C-1" should become "P102C1" (remove 0 and dashes) > > "V1-25-F-2E" should become "125F2E" (remove V and dashes) > > HELP!!
From: Eduardo on 5 May 2010 10:06
Hi,. you can use find and replace, highlight the column , press CTRL + H, find what enter -, replace with don't enter anything and click replace all, do the same with the 0 and V "jackierae17" wrote: > I'm new to Excel. I have a huge inventory spreadsheet where I need to change > a column to reflect the following. > > "P01-02-C-1" should become "P102C1" (remove 0 and dashes) > > "V1-25-F-2E" should become "125F2E" (remove V and dashes) > > HELP!! |