how to keep all leading zeros when stripping dashes from txt num
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. |
how to keep all leading zeros when stripping dashes from txt num
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 |
how to keep all leading zeros when stripping dashes from txt num
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. |
how to keep all leading zeros when stripping dashes from txt num
Sue's is good.
Or you could format cells as Text (@) PRIOR to replacing hyphens. |
All times are GMT +1. The time now is 11:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com