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

S

Sue

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.
 
R

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
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top