Leading Zero and the change command

M

mmccoog

My data is preceeded by "." up to "........" When I use the change command to
delete the ".", it also drops any leading zeros in the text. The field is
formatted as text and the data is not proceeded by a single quote.
Ex. ...01263 changes to 1263. Any ideas why this would happen?
 
P

Pete_UK

When you get rid of the leading full-stops and are left with 01263,
then Excel will treat this as if you typed it in - it comprises only
numeric characters, therefore it must be a number, and any leading
zeroes can therefore be ignored (Excel's logic).

If all your numbers are to be 5 digits in length, then you could use

=RIGHT(A1,5)

or alternatively you could apply a custom format to the cells, but you
will need to give a few more details concerning the variations in your
numbers if you want a more complete answer.

Hope this helps.

Pete
 
M

mmccoog

Thanks Pete. My data is various lengths so instead of deleting the '.', I
changed it to a single quote and that did the trick. Thanks for your response.
Mary Ann
 
Top