Concatenating cells with differing leading zeros

[broncos347]

I have a spreadsheet where i have four columns (B, D, E &F) which hold info which i need to concatenate to create a unique reference number.

The issue i have is that the first column contains text, the second & third contain two digits and the fourth contains three. The second, third & fourth columns all contain leading zeros and when i concatenate them the zero's disappear.

i've search the forum and whilst i can find ways to retain the zeros but the formulas are based on cells with the same number of digits.

any help would be greatly appreciated.

 

[Joe4]

Use the TEXT function to maintain the zeroes, i.e.

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

 

[broncos347]

Joe4, brilliant many thanks. If i wanted to put a space or a hash inbetween each cell what would be the best way?

 

[Joe4]

Just add it in. Remember, concatenate just combines a bunch of strings/values.
So, it would just look something like (dashes):

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

or this (spaces):

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

 

[broncos347]

superb, many thanks.

 

[Joe4]

You are welcome.