Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
1  
Numbers can't have leading zeroes because "3" and "03" are two different representations of the same number. Confusing numbers with number representations will cause you no end of problems using computers and especially programming them. –  David Schwartz Nov 26 '12 at 15:31
add comment

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

enter image description here

share|improve this answer
2  
I think Excel uses semicolons rather than commas to separate parameters in some localisations... (IIRC it's the ones that use commas to indicate the decimal point [radix point]) –  Bob Nov 26 '12 at 14:59
    
Thanks @Bob, I didn't realize that. Updating answer accordingly (although cautiously) –  Dave Rook Nov 26 '12 at 15:08
    
Is there a way to combine columns that are formatted as "custom" and not as "text"? –  Helpneeded Nov 26 '12 at 15:08
    
You can concatenate most formatted cells (it doesn't do well with decimal places though unless it's a string). The issue is whether the cell displays the data as you want. –  Dave Rook Nov 26 '12 at 15:17
    
If the user's data is in a custom format, changing the format of the cell to Text will change how the data is displayed and change what is passed to the concatenation formula. For this reason, changing the format is not a solution. –  Excellll Nov 26 '12 at 21:16
show 1 more comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.