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.

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

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


--
Regards,

Peo Sjoblom

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

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