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

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

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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

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

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

"AuthorizedUserPF" wrote:

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

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.
--
Gary''s Student - gsnu200809


"AuthorizedUserPF" wrote:

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

Thank you all. These all work well.

"AuthorizedUserPF" wrote:

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

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)


--
Thanks,
Shane Devenshire


"AuthorizedUserPF" wrote:

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

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!