Cocatenate not showing leading zeros MS Excel 2003

[AuthorizedUserPF]

Hi all;

I am trying to cocatenate three columns of data into 1. No problem , but I
am having trouble trying to get cocatenate to accept leading zeroes. I have
one spreadsheet where the data is run from Oracle (Peoplesoft) to Excel and a
standard 10 digit number will come over with leading zeroes. I.E. 0000007899.
When I cocatenate with other columns, the cocatenate accepts the leading
zeroes.
Column a1 = MIS, Column B1 = 1, Column C1 = 0000007899, Cocatenate =
MIS10000007899
Now I have a Spreadsheet where the data must have been sent via CSV, text,
prn.
Column D1 = MIS, Column E1 = 1, and Column F1 = 7899. I used a new column,
G1 to do a = value formula on column F1 , 7899, and custom format as number
0000000000. The number now displays as 0000007899 in column G1. Now when I
cocatenate Columns D1, MIS, E1, 1, and G1, 0000007899, I get in return
MIS17899. I do I get the leading Zeroes in the cocatenate so I can use it as
a vlookup value.
Any help will be greatly appreciated
Thanks Phil

 

[RagDyer]

No need for G1 ... do the formatting right in the concatenate formula.

=D1&E1&TEXT(F1,"0000000000")

 

[~L]

Instead of =Value, try =TEXT(F1,"0000000000")

When formatting as a number, leading zeros are ignored/erased.

 

[Gary''s Student]

In general, if you concatenate values with custom formats, you need to
re-assert the formats to get them to appear

If A1 contains:
MIS
and A2 contains 1 formatted as 00

the formula =A1 & A2 will display MIS1 and not the desired MIS01.
To get MIS01, use this formula:
=A1 & TEXT(A2,"00")

The TEXT() re-asserts the format in the concatenation.

 

[AuthorizedUserPF]

Thank you all. These all work well.

 

[ShaneDevenshire]

Hi,

Although its not as general, you could use
=A1&B1&"000000"&C1

and an old standard which is general:
=A1&B1&RIGHT("0000000000"&C1,10)

 

[Snailspace]

Do this happen with 2007? Is this part of Excel's program? Seems to
me you would want it to maintain the data you have entered.

Thanks!