Working with numbers in Excel-Specify # of digits

[George]

I have a range of numbers; some are 7 digits long, others, 8, 9 or 10 digits.
How can I tell Excel to put zeroes in front of the numbers that don't have
full ten digits? I want the result to be ten digits long for everything.
Excel 2003. Hopefully this is an easy one for most! Thanks for the help.

 

[Jim May]

I have a range of numbers; some are 7 digits long, others, 8, 9 or 10 digits.
How can I tell Excel to put zeroes in front of the numbers that don't have
full ten digits? I want the result to be ten digits long for everything.
Excel 2003. Hopefully this is an easy one for most! Thanks for the help.

Select the cells (highlight) and Format, Cells.., Numbers (Tab), Select
Custom and in the General textbox enter: 0000000000

 

[Peo Sjoblom]

Use a custom format of 0000000000

--


Regards,


Peo Sjoblom

 

[T. Valko]

Use a custom number format.

Select the cells in question
Goto the menu Format>Cells>Number tab>Custom
Enter 10 zeros in the little box under TYPE:
0000000000
OK out

 

[Teethless mama]

format cell as: 0000000000

or using formula

=TEXT(A1,"0000000000")

 

[George]

Now I can't sort the data, taking into account the zeroes. Does my entire
list of numbers need to be formatted a special way to force the zeroes into
order? It seems that currently the sorting is reliant on actual numbers in
the cells. Thanks.

 

[T. Valko]

The formatting method of padding with leading zeros doesn't change the
underlying value of the cell. Formatting is for *display purposes only*.

If the cell contains 123 and the formatted display value is 000123 the true
underlying value of that cell is 123 so Excel is sorting based on 123 not
000123.

You'd have to convert the result to TEXT and then Excel should sort as you
want:

A1 = 123

=TEXT(A1,"0000000000")

Returns the TEXT string 0000000123