Concatenating with numbers

[Kurganscott]

I have need to combine 5 columns into one field. Using the CONCATENATE function accomplishes this but in fields that begin with a zero, the formula (or possibly the format of the target cell, although I have tried variations with no success) removes the zeros leaving on the number at the end. The needed final result is a coding field for another program that requires those zeros.

Anybody? Bueller? Thanks!

 

[Andrew Poulsom]

Post a sample of your data and the result you want.

 

[Oaktree]

Welcome to the board.

Try:

=TEXT(CONCATENATE(A1,B1,C1,D1,E1),"00000")

 

[Kurganscott]

Sample... Five Fields. I tried the above (thanks for the tip and welcome!) but to no avail. I appreciate your quick responses all!

D 0001 1 00 001

Scott

 

[Oaktree]

I assume those are the values in those cells (as opposed to the second cell having the value of 1 but being formatted as 0000 to display 0001).

What is the desired result?

 

[Kurganscott]

D0001100001 would be the desired result (11 characters from 5 fields)

Field 1 = D
Field 2 = 0001
Field 3 = 1
Field 4 = 00
Field 5 = 001

 

[Andrew Poulsom]

D0001100001 would be the desired result (11 characters from 5 fields)

Field 1 = D
Field 2 = 0001
Field 3 = 1
Field 4 = 00
Field 5 = 001

Like this?

=A1&TEXT(B1,"0000")&C1&TEST(D1,"00")&TEXT(E1,"000")

 

[Oaktree]

(There is a typo in Andrew's formula. Should be

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

 

[NBVC]

Try:

=(a1&TEXT(b1,"0000")&c1&TEXT(d1,"00")&TEXT(e1,"000"))

 

[Kurganscott]

GOD love you guys! This is how it ended up, worked PERFECTLY!

=B2&TEXT(C2,"0000")&D2&TEXT(E2,"00")&TEXT(F2,"000")

Thanks a bunch all, if you are ever in Louisville, Ky look me up, we'll do lunch!

What a great website, I had no idea. Firmly placed in my favorites now however. I have been hacking my way through excel for years now. AHHH the power of the internet...