Leading 0's disappear

[Nick]

Ok, heres my dilema, I deal with a lot of serial numbers, many of which begin
with a 0, and well it is very time consuming to go through and enter '0
infront of all of them. Is there any way to turn off the auto-correct
feature that gets rid of leading zeros in my cells?

 

[Max]

Let's say you have already entered data in A1:A10
Put in B1: ="0"&A1
Copy down to A10. Then copy B1:B10, paste over A1:A10 with a paste special
as values. This "restores" the leading zero to the data at one go. Clear
B1:B10.

For new data entries,
just pre-format the range as TEXT before you enter the data
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik

 

[ShaneDevenshire]

Hi,

First the answer to your question - no.

But besides concatenating a leading 0 as already suggested, you might be
able to handle your problem with a format. If 1. all entries are the same
length, say 5 characters, then you can create the custom format 00000. You
do this by choosing the command Format, Cells, Number tab, Custom and
entering 00000 on the Type line. 2. This method would also address the
problem if you wanted all numbers displaying 5 digits but some of them only
had 1 non-zero digit, such as "9". The above format would display as 00009.