Leading Zero and the change command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mmccoog - 06 Jun 2007 22:46 GMT

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?

Reply to this Message

Pete_UK - 07 Jun 2007 00:54 GMT

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

> 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?

Reply to this Message

mmccoog - 12 Jun 2007 21:37 GMT

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

> 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
[quoted text clipped - 17 lines]
> > formatted as text and the data is not proceeded by a single quote.
> > Ex. ...01263 changes to 1263. Any ideas why this would happen?

Reply to this Message