Excel - Concatenate many columns

Question

I'm trying to concatenate a bunch of columns in Excel. I know I can manually do with:

=A1&", "&B1&", "&C1 (and so on)

but I have about 40 columns, and I'm looking for a way to streamline this process.

Thanks in advance for any help!

Answer 1

When concatenating a range single row or column you can do this in a single shot using Application.Transpose to avoid range loops

This UDF has three arguments

1. A 1D range (can be a column or row)
2. An optional delimiter (, is used if there is no entrey)
3. An optional entry to specify if the range is a row (enter TRUE for a range - which on further thought I will update the UDF to automatically detect whether the range is row OR column BASED)

Note that in terms of the other answers

• IIF evaluates both the TRUE and FALSE arguments as VBA doesn't [short circuit]( http://en.wikipedia.org/wiki/Short-circuit_evaluation). So IFF can be expensive inside loops
• when concatenating join the long string to the combined output of the short strings, rather than long with short, then long with short again

code

Function ConCat(rng1 As Range, Optional StrDelim As String, Optional bRow As Boolean) As String
Dim x
If StrDelim = vbNullString Then StrDelim = ","
x = Application.Transpose(rng1)
If bRow Then x = Application.Transpose(x)
ConCat = Join(x, StrDelim)
End Function

In the example below

• the formula (D1) is =concat(A1:C1,",",TRUE)
• the formula in E1 is =concat(E3:E5,", ")

Answer 2

Let me post my function too. I've run into this problem as well in the past.
My problem usually arise when I try to concatenate dates, errors and blank cells.
So I try to cover most of those using below:

Function CONCATPLUS(ref_value As Range, Optional delimiter As Variant) As String
    Dim cel As Range
    Dim refFormat As String, myvalue As String

    If ref_value.Cells.Count = 1 Then CONCATPLUS = CVErr(xlErrNA): Exit Function
    If IsMissing(delimiter) Then delimiter = " "

    For Each cel In ref_value
        refFormat = cel.NumberFormat
        Select Case TypeName(cel.Value)
        Case "Empty": myvalue = vbNullString
        Case "Date": myvalue = Format(cel, refFormat)
        Case "Double"
            Select Case True
            Case refFormat = "General": myvalue = cel
            Case InStr(refFormat, "?/?") > 0: myvalue = cel.Text
            Case Else: myvalue = Format(cel, refFormat)
            End Select
        Case "Error"
            Select Case True
            Case cel = CVErr(xlErrDiv0): myvalue = "#DIV/0!"
            Case cel = CVErr(xlErrNA): myvalue = "#N/A"
            Case cel = CVErr(xlErrName): myvalue = "#NAME?"
            Case cel = CVErr(xlErrNull): myvalue = "#NULL!"
            Case cel = CVErr(xlErrNum): myvalue = "#NUM!"
            Case cel = CVErr(xlErrRef): myvalue = "#REF!"
            Case cel = CVErr(xlErrValue): myvalue = "#VALUE!"
            Case Else: myvalue = "#Error"
            End Select
        Case "Currency": myvalue = cel.Text
        Case Else: myvalue = cel
        End Select

        If Len(myvalue) <> 0 Then
            If CONCATPLUS = "" Then
                CONCATPLUS = myvalue
            Else
                CONCATPLUS = CONCATPLUS & delimiter & myvalue
            End If
        End If
    Next
End Function

As of now, I've not encountered a cell entry this function cannot concatenate.
Feel free to adjust to your needs or hearts content. HTH.

Answer 3

You can always use Visual Basic For Applications (VBA). It is microsofts language for Office. Here is an example of what you might be looking for but try the Google Machine to learn more about VBA and how to input this code into your spreadsheet.

Sub ConcatColumns()

Do While ActiveCell <> "" 'Loops until the active cell is blank.

  'The "&" must have a space on both sides or it will be
  'treated as a variable type of long integer.

  ActiveCell.Offset(0, 1).FormulaR1C1 = _
     ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)

  ActiveCell.Offset(1, 0).Select

Loop

End Sub

Answer 4

As a user function taking a range

Public Function ClarkeyCat(ByRef rng As Range) As Variant

Dim c As Range
Dim ans As Variant

For Each c In rng

If (c.Value <> "") Then
    ans = IIf(ans = "", "", ans & ",") & c.Value
End If

Next

ClarkeyCat = ans
End Function

Changing the Variant types, if you need to (to string, most likely).

Use like this:

Answer 5

I would use vba for this. For each column you would want something like (assuming values are in row 1)

myString = ""
for i = 1 to 40
  if i <> 40 then
    myString = myString & Cells(1, i) & ", "
  else:
    myString = myString & Cells(1, i)
  end if
next i

myString will then have the contents of your concatenated string.