Concatenate Range 1

The Excel’s concatenate formula, or operator & do not work with ranges. You have to specify each parameter you want to add to the string. Sometimes it may get annoying, especially when the range is dynamic and depends on some parameters. Then it would be useful to have a formula that can work with ranges, and therefore also with indirect addressing.

The function below solves the problem

'' Concatenate values from every cell across the cell_range separated with separator
Public Function ContactenateRange(ByVal cell_range As Range, _ 
           Optional ByVal separator As String = "") As String
    Dim cell As Range, newString As String, cellArray As Variant
    Dim i As Long, j As Long
    cellArray = cell_range.Value
    For i = 1 To UBound(cellArray, 1)
        For j = 1 To UBound(cellArray, 2)
            If Len(cellArray(i, j)) <> 0 Then
                newString = newString & (separator & cellArray(i, j))
            End If
        Next j
    Next i
    If separator <> "" Then
        ContactenateRange = Mid$(newString, 2)
        ContactenateRange = newString
    End If
End Function

The use is pretty straightforward. In Excel’s cell you just write =ConcatenateRage(A1:A5) and viola. In case you want to add a separator just add it as a second argument. The function works with character, numeric, dates, pretty much everything. You may find sample use in the screen below.

Sample use of VBA function ConcatenateRange.

Sample use of VBA function ConcatenateRange.

One comment on “Concatenate Range

  1. Reply dancome Jul 14,2016 03:04

    Hello ~ Awesome article ~ Thank You

Leave a Reply