Concatenate Ranges, Arrays, and Values Using Excel VBA

If you have Office 2019 or a subscription to Microsoft 365, then you have access to a useful Excel function named TEXTJOIN. This function concatenates text across ranges, arrays, and individual values and combines them into a single cell value. This function eliminates a multi-step process that typically involves concatenating individual cells across a range, copying/pasting the results to a text editor, and then removing line breaks.

As an alternative for those who do not have access to TEXTJOIN, the following is a user-defined function (UDF) named CONCATENATE_RANGE that mimics most of the TEXTJOIN functionality with similar performance.

Usage

CONCATENATE_RANGE(delimiter, ignore_empty, row_priority, text1, [text2], …)

The CONCATENATE_RANGE function evaluates each of the supplied ranges, arrays, and values and concatenates (combines) the identified values together into a single string separated by the supplied delimiter.

ArgumentRequiredDescription
delimiterYesA text string used to separate each concatenated value. The delimiter may be an empty string, one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text.
ignore_emptyYesIf TRUE, the function will ignore any empty values.
row_priorityYesIf TRUE, the function will first concatenate each value in a row first (moving across columns); if FALSE, the function will first concatenate each value in a column (moving down rows).
text1YesValue to concatenate. Parameter may be a text string, a cell range, or an array. If a number is supplied, it will be treated as text.
[text2, …]NoAdditional values to concatenate. Parameter may be a text string, a cell range, or an array. If a number is supplied, it will be treated as text.

Known Limitations

If the result exceeds 32767 characters, including the delimiter, the function returns the #VALUE! error. This is due to the inherent cell length limitation in Excel.

The function also does not work on 3D ranges (ranges that span multiple worksheets).

Creating the Function

Step 1 – Open Visual Basic

From the Developer ribbon in Excel, click Visual Basic.

The Microsoft Visual Basic for Applications window opens.

Step 2 – Insert New Module

From the Insert menu, click Module.

Excel VBA – Insert Module
Excel VBA – Insert Module
Excel VBA – New Module
Excel VBA – New Module

Step 3 – Add Code

Add the below code to the newly created module.

Step 4 – Close Visual Basic

Close the Microsoft Visual Basic for Applications window.

Result

All results from the following examples are displayed in the screenshot below.

Example 1 – TEXTJOIN

This example is used as the baseline to compare with the result from CONCATENATE_RANGE on the same data set.

=TEXTJOIN(", ",TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 2 – CONCATENATE_RANGE (row priority)

This example demonstrates that the result from CONCATENATE_RANGE matches the result from TEXTJOIN on the same data set.

=CONCATENATE_RANGE(", ", TRUE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 3 – CONCATENATE_RANGE (column priority)

When compared with Example 2, column priority sequences the resulting string by each entry in a column first.

=CONCATENATE_RANGE(", ", TRUE, FALSE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 4 – CONCATENATE_RANGE (row priority, include empty)

Similar result as Example 2 except empty values are included in the result.

=CONCATENATE_RANGE(", ", FALSE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 5 – CONCATENATE_RANGE (row priority, no delimiter)

Similar result as Example 2 except no delimiter is used to separate values.

=CONCATENATE_RANGE("", TRUE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)
Excel – CONCATENATE_RANGE Examples
Excel – CONCATENATE_RANGE Examples

Source Code

Option Explicit

Function CONCATENATE_RANGE(delimiter As String, ignore_empty As Boolean, row_priority As Boolean, ParamArray data()) As String
  Dim arrayData() As Variant
  Dim lParamArrayIterator As Long
  Dim vDataArrayIterator As Variant
  Dim sResult As String: sResult = ""

  For lParamArrayIterator = LBound(data) To UBound(data)
    If IsObject(data(lParamArrayIterator)) Or IsArray(data(lParamArrayIterator)) Then
      If IsObject(data(lParamArrayIterator)) Then
        If data(lParamArrayIterator).Cells.Count = 1 Then
          ReDim arrayData(1 To 1, 1 To 1)
          arrayData(1, 1) = data(lParamArrayIterator).Value2
        Else
          arrayData = data(lParamArrayIterator).Value2
        End If
      Else
        arrayData = data(lParamArrayIterator)
      End If

      If row_priority Then
        arrayData = Application.WorksheetFunction.Transpose(arrayData)
      End If

      For Each vDataArrayIterator In arrayData
        If ((ignore_empty And Not IsEmpty(vDataArrayIterator)) Or (Not ignore_empty)) Then
          sResult = sResult & vDataArrayIterator & delimiter
        End If

        If Len(sResult) > 32767 Then
          CONCATENATE_RANGE = CVErr(xlErrValue)
        End If
      Next vDataArrayIterator
    Else
      If ((ignore_empty And Not IsEmpty(data(lParamArrayIterator))) Or (Not ignore_empty)) Then
        sResult = sResult & data(lParamArrayIterator) & delimiter
      End If

      If Len(sResult) > 32767 Then
        CONCATENATE_RANGE = CVErr(xlErrValue)
      End If
    End If
  Next lParamArrayIterator

  If (Len(sResult) >= Len(delimiter)) Then
    sResult = Left(sResult, Len(sResult) - Len(delimiter))
  End If

  CONCATENATE_RANGE = sResult
End Function

Leave a Comment