How to remove specfic dashes within a cell, leaving others

[JoeNolan]

I have a series of columns that have dashes in them and want to remove just specific dashes from the cell

Example of original 70489-344-15-001

This is what I want --> 70489-34415001


Got about 1000 of them ...

Thanks in advance

[Speshul]

Two methods:

=TEXT(SUBSTITUTE(D2,"-",""),"00000-00000000")

=LEFT(TRIM(SUBSTITUTE(D3,"-","")),5)&"-"&RIGHT(TRIM(SUBSTITUTE(D3,"-","")),8)


Those will only work if the number is always going to be five digits - 8 digits though... 12345-12345678. The latter will allow you to adjust how many digits are on either side of the hyphen by replacing the bold 5 and/or 8 with another formula or cell reference.

[Paul]

If all of your starting values follow the same pattern (value has three dashes to start with, and you only want to keep the first one), try:

=SUBSTITUTE(SUBSTITUTE(A1,"-","",2),"-","",2)

If you have more than three dashes this will require adjustment.