Stripping dashes from a number

[Rohit Thomas]

Hello All,

I am working with SS #'s that are imported into an excel
spreadsheet from a delimited text file. The SS #'s in the
text file have dashes that I need to strip. I used the
find\replace method in a macro to do this but the problem
I run into is that it removes the leading zero in certain
SS #'s that have a zero as the first number. I tried
changing the cell format to text before stripping the
dashes but that did not work. Any suggestions on how I can
remove the dashes and keep the leading zeros?

Thanks,
Rohit Thomas

 

[Dave Peterson]

Can you do the edit|replace and then apply a custom number format: 000000000

 

[Patrick Molloy]

The issue is that a text string like 0123-456-789
will become 0123456789 and so excel saves this as a
number, 123456789, dropping any preceding zero's.
solution, add a single quote at the front of the text
string.

OldText = "012-345-67-89"

NewText = "'" & Replace(OldText,"-","")

now this will be saved to the sheet as text - the sngle
quote will not be seen.

Patrick Molloy
Microsoft Ecel MVP