how to keep all leading zeros when stripping dashes from txt num

[Guest]

I have a text column that contains formatted numbers.
I need to strip the dashes, but preserve ALL numbers.
When I use replace to strip the dashes, Excel automatically changes the
cells to numbers format and I lose all the leading zeros.

 

[Rick Rothstein \(MVP - VB\)]

I have a text column that contains formatted numbers.

I need to strip the dashes, but preserve ALL numbers.
When I use replace to strip the dashes, Excel automatically
changes the cells to numbers format and I lose all the leading zeros.

This seems to work...

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

where you would use your own cell reference in place of the A1.

Rick

 

[Peo Sjoblom]

Use a custom format like 000000

or how many digits you need, so if you want to display 12284567 as

012284567

and you always want to display 9 digits use a custom format of

000000000


note that if you have more than 15 digits it won't work, Excel's maximum
when it comes to a number is 15 digits so you can't numerically format
credit card numbers. Only text will work there

 

[Guest]

Sue's is good.
Or you could format cells as Text (@) PRIOR to replacing hyphens.