Excel – Concatenating numbers that have leading zeros

concatenationmicrosoft excelmicrosoft-excel-2010

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).

Best Answer

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'.

enter image description here

Related Solutions

Excel – Best way to remove leading zeros from a non-numeric value in Excel

Here's a solution that's cell-intensive but correct.

Put your data in column A.

In B1, put the formula:

=IF(
    LEFT(A1) = "0" ,
    RIGHT(A1, LEN(A1)-1),
    A1)

This checks for a single leading zero and strips it out.

Copy this formula to the right as many columns as there can be characters in your data (9, in this case, so you'll be going out to column J). Copy it down for each row of data.

The last column contains your data, stripped of leading zeros.

Excel – Why does Excel 2010 automatically reformat numbers

You don't have to change the cells' formatting one at a time. You can apply the same formats to whole columns, ranges or even sheets. If you know that you'll have number in all the cells, make sure you set them to one of the number formats. The General formatting option is treated by Excel as a sort of "no formatting" option, so it will often try to guess what specific format you want it in.

Alternatively, you may want to look at this answer and see if it applies to your situation:

Excel assumes you have not applied a format if you use general, so it tries to find the best format for the data you entered. To disable this, go to File => Options => Advanced and uncheck the option for "Extend data range formats and formulas."

Best Answer

Related Solutions

Excel – Best way to remove leading zeros from a non-numeric value in Excel

Excel – Why does Excel 2010 automatically reformat numbers

Related Question