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.

in article , George at
wrote on 9/5/08 1:51 PM:

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

Use a custom format of 0000000000

--


Regards,


Peo Sjoblom

"George" wrote in message
...
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.

Use a custom number format.

Select the cells in question
Goto the menu FormatCellsNumber tabCustom
Enter 10 zeros in the little box under TYPE:
0000000000
OK out

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
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.

format cell as: 0000000000

or using formula

=TEXT(A1,"0000000000")


"George" wrote:

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.

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

Use a custom number format.

Select the cells in question
Goto the menu FormatCellsNumber tabCustom
Enter 10 zeros in the little box under TYPE:
0000000000
OK out

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
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.

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

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
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" wrote:

Use a custom number format.

Select the cells in question
Goto the menu FormatCellsNumber tabCustom
Enter 10 zeros in the little box under TYPE:
0000000000
OK out

--
Biff
Microsoft Excel MVP


"George" wrote in message
...
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.