Concatenating numbers that have leading zeros

I'm trying to combine/concatenate numbers in different columns into one long number. Some of the numbers have leading zeros because of custom formats applied to the cells. When I concatenate them together, the zeros are removed. Does someone know how the formula should look so that the leading zeros aren't removed?

Example:
A1 = 08,
B1 = 7,
C1 = 0,
D1 = 17,
E1 = 00,
F1 = 01,
G1 = Concentrated number from A1 to F1

The resulting number should be 0870170001 however the following number appears 8701701.

I'm using Excel 2010, and this is the formula I've tried: =CONCATENATE(A1;B1;C1;D1;E1;F1).

2 Answers

You may not need the ; but instead, use a comma to show each is a separate string value (this may depend on localization settings - Thanks Bob).

=CONCATENATE(A1,B1,C1,D1,E1,F1)

However, you will need to make sure that the cells with the numbers in are formatted to text by highlighting the row and in the Home ribbon, under Number tab, in the drop down list select 'Text'.

I see from your comment that your cells most likely have custom formats, and the displayed value in these cells is likely different from the entered value (e.g. "8" is shown as "08"). In this case, you can concatenate each value wrapped in a TEXT function with the specified format of the cell. For example, if A1 has custom format "00", you would use TEXT(A1,"00") as the term for A1 in your concatenation formula. For the full formula, you may have something like this:

=TEXT(A1,"00")&TEXT(B1,"@")&TEXT(C1,"0")&...

Of course, this is tedious as it requires you to manually recreate the format of each cell. If you're going to be using this a lot with lots of varied formats, I would say this is a perfect opportunity to use a VBA function to do the heavy lifting. You can paste the following code into a module in the VBA Editor (opened by pressing Alt+F11).

Public Function CONCATwFORMATS(rng1 As Range) As String
Dim tmpstr As String, tmpFormat As String
Dim c As Range
For Each c In rng1 tmpFormat = c.NumberFormat If tmpFormat = "General" Then tmpstr = tmpstr & c.Value Else tmpstr = tmpstr & Format(c.Value, tmpFormat) End If
Next c
CONCATwFORMATS = tmpstr
End Function

Then, you can use the following formula in G1.

=CONCATwFORMATS(A1:F1)

This is just a quick attempt at this code, so note two requirements of the function:

1. It only takes a contiguous range as an argument.
2. It concatenates the values in order from left to right (and top to bottom).

The code can be tweaked to remove these restrictions, but for what you want, it sounds like this will work fine.