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!

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.

"klieske" wrote:

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!

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.


--
bigbri
------------------------------------------------------------------------
bigbri's Profile: http://www.excelforum.com/member.php...o&userid=22213
View this thread: http://www.excelforum.com/showthread...hreadid=534970

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" wrote:

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.

"klieske" wrote:

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!

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" wrote:

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" wrote:

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.

"klieske" wrote:

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!

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

"SVC" wrote:

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" wrote:

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" wrote:

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.

"klieske" wrote:

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!

"klieske" wrote in message
...
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.
--
David Biddulph