I have a couple columns of numbers that needed zeros added to them as
follows:

A B C
145 29 812
125 8 4

need to be formatted as

145 29 812
125 08 004

Using format each column using Custom. Format the 2 digit column as 00, and
the 3
digit column as 000 the columns work fine.

But...I then need to use the columns in a combinations formula =A2&B2&C2 to
have a resulting 8 digit number...but the 'custom' 0's do not convert...I
get a number like 12584 instead of the needed 12508004 ?

Anyway around this issue ?

Thanks, Tim

One way:

=TEXT(A1,"000") & TEXT(B1,"00") & TEXT(C1,"000")

another:

=TEXT(A1*100000 + B1*1000 + C1, "00000000")

In article ,
"Tim" wrote:

I have a couple columns of numbers that needed zeros added to them as
follows:

A B C
145 29 812
125 8 4

need to be formatted as

145 29 812
125 08 004

Using format each column using Custom. Format the 2 digit column as 00, and
the 3
digit column as 000 the columns work fine.

But...I then need to use the columns in a combinations formula =A2&B2&C2 to
have a resulting 8 digit number...but the 'custom' 0's do not convert...I
get a number like 12584 instead of the needed 12508004 ?

Anyway around this issue ?

Thanks, Tim

As you have found, formatting for display does not change the underlying value
of the data.

In D2 enter =TEXT(A2,"000") & TEXT(B2,"00") & TEXT(C2,"000")

Copy D2 and paste specialvalues.

The data will be in text format and calculations can't take place.

To overcome that..................

Select D2 and DataText to ColumnsNextNextColumn Data FormatGeneral and
Finish.


Gord Dibben MS Excel MVP


On Sun, 21 Oct 2007 06:45:50 -0700, "Tim" wrote:

I have a couple columns of numbers that needed zeros added to them as
follows:

A B C
145 29 812
125 8 4

need to be formatted as

145 29 812
125 08 004

Using format each column using Custom. Format the 2 digit column as 00, and
the 3
digit column as 000 the columns work fine.

But...I then need to use the columns in a combinations formula =A2&B2&C2 to
have a resulting 8 digit number...but the 'custom' 0's do not convert...I
get a number like 12584 instead of the needed 12508004 ?

Anyway around this issue ?

Thanks, Tim