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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
Click to expand...

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
 
Back
Top