How do I keep 2 zero's in front of s/n in a cell

[klieske]

I have a long spreadsheet for my inventory in a library and for some reason
the 2 zero's will not stay after i put in a number. Such as: 0033995753.
The zero's get dropped and I cannot figure out how to get them back. I am
using Office Pro 2003. Thank you!

 

[SVC]

If all the numbers in your column have the same number of digits, use Format

Cell, on the Number tab select Custom and (based on a number with a maximum

length of 10 digits) enter 00##########. This will force the leading two 0's
to remain for any number up to 10 digits.

Alternately, you can format your colum as Text before entering numbers.

 

[bigbri]

I've found that if I need a standard formatting such as this, and
they're all the same standard length, it works better if you use all
zeros in the custom formatting such as "0000000000" (without the
quotes, of course). This way Excel will always put it as a 10 digit
number regardless of the value.

 

[klieske]

Is there a way to fix this on preexisting spreadsheets? It is pretty long
and I tried it that way but it wouldn't change. Thanx...Kelly

 

[SVC]

It might be that you have a mixture of number and text items. Try this:

1. In a blank cell type the number one (1).
2. Copy the cell.
3. Select the entire range with you inventory numbers.
4. Edit > Paste Special, under Operation select Multiple, then click OK.
5. Now try applying the Custom format.

Alternately, if the numbers are already text, you can concatenate. If A1
contains, e.g. 123456, in a blank cell in row 1 type ="00"&a1, then copy
down. This will add leading 00 to the number. When done, copy the entire
column, then Edit > Paste Special, select Value.

 

[klieske]

I will try that and let you know. Thanx alot. Kelly

 

[David Biddulph]

I have a long spreadsheet for my inventory in a library and for some reason
the 2 zero's will not stay after i put in a number. Such as: 0033995753.
The zero's get dropped and I cannot figure out how to get them back. I am
using Office Pro 2003. Thank you!

You need to format the cells as text, not number, before you enter the data.