How do I create a formula to remove dashes?
 

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!!

How do I create a formula to remove dashes?
 

highlight the column header (ie C)editreplace - blank

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"jackierae17" wrote in message
...
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!!

How do I create a formula to remove dashes?
 

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!!

How do I create a formula to remove dashes?
 

Just use Find & Replace (CTRL-H). Highlight the column by clicking on
the column header, then CTRL-H, and:

Find what: -
Replace with: (leave blank)
Click Replace All

Hope this helps.

Pete

On May 5, 2:46*pm, 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!! *

How do I create a formula to remove dashes?
 

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!!

How do I create a formula to remove dashes?
 

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!!