Concatenate Multiple Cells in Excel
Presenting a user-defined function to easily concatenate multiple cells in Excel

Ronald February 14, 2014

Don’t you find it hard that for combining text from multiple cells, you have to select individual cells rather than selecting a range? One can easily create a user-defined function in Excel to add this functionality and make it easier to concatenate with a delimiter.

Below, I present a user-defined function (UDF) that you can add to an Excel add-in.

For an easy overview on how to create a user-defined function, look at the tutorial How to Build an Excel Add-in .

With this UDF, it’s possible to concatenate an unlimited number of ranges using a ParamArray. The delimiter is a required parameter.

Example usage: =CONCATRANGES(";",A1:A3,B5,C6:D7,"X",1,{"A","B","C"})

Since Excel2000 you can use powerful VB functions in VBA, such as Join instead of concatenate operator (&). Continuous concatenation of strings can become very slow with large sets of data. Join is the optimized function for doing this, even though you need to populate the array first. It might be overkill to do it like this in most use cases, but it is certainly fast.

The code for the function: CONCATRANGES()

Public Function CONCATRANGES(ByVal sDelim As String, ParamArray rRanges()) As String
    Dim rRange, rCell As Excel.Range, aVal() As String, i As Long, iSize As Long, sType As String, vCell As Variant
    
    iSize = 0
    
    i = 1
    For Each rRange In rRanges
        sType = TypeName(rRange)
        Select Case sType
            Case "Range"
                iSize = iSize + rRange.Count
                ReDim Preserve aVal(1 To iSize)
                For Each rCell In rRange
                    aVal(i) = rCell.Text
                    i = i + 1
                Next
            Case "Variant()"
                iSize = iSize + UBound(rRange)
                ReDim Preserve aVal(1 To iSize)
                For Each vCell In rRange
                    aVal(i) = vCell
                    i = i + 1
                Next
            Case Else
                iSize = iSize + 1
                ReDim Preserve aVal(1 To iSize)
                aVal(i) = rRange
                i = i + 1
        End Select
    Next
    
    CONCATRANGES = Join(aVal, sDelim)
End Function

Let's explain what's in the function. rRanges is a ParamArray, and this needs to be iterated in the function to find all ranges referenced by the user. There are several types of values and ranges that may be referenced in a function. Normally, users will only use actual cell references in their formulas. But some users may decide to have a regular value (e.g. "X" or 1.2) in the formula. In an advanced scenario, one can even use an array of values (e.g. {"A","B","C"}).

Therefore, the function looks at the type of a cell, which can be Range (regular cell reference), Variant() (array of values) or other (regular value). Each needs to be treated in a different way.

I hope you will find this function useful. Please let me know how you are using it!