Concatenate

[santoshdream]

Here is my problem in excel..

B2 C2 D2 E2 F2
00038 371 0010170001 0270 38396


=CONCATENATE(B2," ",C2," ",D2," ",E2," ",F2)


Result is comming ----- 38 371 10170001 270 38396

But I need this result---- 00038 371 0010170001 0270 38396

Please find the attached.
Please help
Thanks and regards

 

[Mr. B]

Give this a try:

=CONCATENATE(B3," ",C3," ","00",D3," ",E3," ",43)

 

[santoshdream]

Hello,

Its not working as zeros are missing...

 

[Mr. B]

Oops, sorry,

In only made the middle set of characters work.

Try this one:
=CONCATENATE("000",B3," ",C3," ","00",D3," ","0",E3," ",F3)

 

[HaHoBe]

Hi,

=RIGHT("00000" & B2,5) & " " & RIGHT("000" & C2,3) & " " & RIGHT("000000000" & D2,10) & " " & RIGHT("0000" & E2,4) & " " & RIGHT("00000" & F2,5)

Ciao,
Holger

 

[CHSBK]

Hi

If the original columns are formatted as text in order to keep any number of leading zeros and the cell in which you want the result is left with a general format, then the following will work:

=CONCATENATE(B2, " ", C2, " ", D2, " ", E2, " ", F2)

See attached example.

 

[Brianwarnock]

If you don't want to change your spreadsheet convert to text on the fly for those columns with leading zeroes eg
=CONCATENATE(TEXT(B3,"00000")," ",C3," ",TEXT(D3,"0000000000")," ",TEXT(E3,"0000")," ",F3)

Brian