Concatenate Excel Ranges, Arrays, and Values Without TEXTJOIN Using VBA

If you’re using Office 2019 or a Microsoft 365 subscription, Excel includes a helpful function called TEXTJOIN. It allows you to join text from ranges, arrays, or individual values into a single cell. This function streamlines what used to be a multi-step process involving manual concatenation and formatting.

However, if you don’t have access to TEXTJOIN, you can use the VBA user-defined function (UDF) described in this article. It mimics much of the TEXTJOIN functionality and performs similarly.

Instructions

Create a New VBA Module

1. From the Developer ribbon in Excel, click Visual Basic.
2. In the Visual Basic for Applications window, go to Insert > Module.
3. Paste the following code into the new module and close the editor.

Option Explicit

Function ConcatenateRange( _
  delimiter As String, _
  ignoreEmpty As Boolean, _
  rowPriority As Boolean, _
  ParamArray data() _
) As String

  Dim arrData() As Variant
  Dim lParamIndex As Long
  Dim vItem As Variant
  Dim sResult As String: sResult = ""

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

      If rowPriority Then
        arrData = Application.WorksheetFunction.Transpose(arrData)
      End If

      For Each vItem In arrData
        If (ignoreEmpty And Not IsEmpty(vItem)) Or (Not ignoreEmpty) Then
          sResult = sResult & vItem & delimiter
        End If

        If Len(sResult) > 32767 Then
          ConcatenateRange = CVErr(xlErrValue)
          Exit Function
        End If
      Next vItem
    Else
      If (ignoreEmpty And Not IsEmpty(data(lParamIndex))) _
        Or (Not ignoreEmpty) Then
        sResult = sResult & data(lParamIndex) & delimiter
      End If

      If Len(sResult) > 32767 Then
        ConcatenateRange = CVErr(xlErrValue)
        Exit Function
      End If
    End If
  Next lParamIndex

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

  ConcatenateRange = sResult
End Function

Usage

The ConcatenateRange(delimiter, ignore_empty, row_priority, data()) function joins values from ranges, arrays, and individual cells into a single string, separated by the specified delimiter.

Parameters:

• delimiter (required): Text that separates each value. It can be empty (""), a string (e.g., ", "), or a cell reference. Numbers are treated as text.
• ignore_empty (required): If TRUE, the function skips empty values.
• row_priority (required): If TRUE, joins values by row (left to right). If FALSE, joins values by column (top to bottom).
• data1 (required): The first value, range, or array to concatenate.
• [data2, ...] (optional): Additional values, ranges, or arrays to include.

Results

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

Example 1: TEXTJOIN (for comparison)

This example is used as a baseline to compare the results from ConcatenateRange.

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

Example 2: ConcatenateRange (row priority)

This example demonstrates that the result from ConcatenateRange matches the result from TEXTJOIN with the same arguments.

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

Example 3: ConcatenateRange (column priority)

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

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

Example 4: ConcatenateRange (row priority, include empty)

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

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

Example 5: ConcatenateRange (row priority, no delimiter)

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

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

Known Limitations

• The result string cannot exceed 32,767 characters (Excel’s cell limit).
• The function does not support 3D ranges (across multiple sheets).

Summary

If you don’t have access to TEXTJOIN, the ConcatenateRange user-defined function is a reliable replacement. It provides flexibility with delimiters, empty values, and data layout. With a few lines of VBA, you can streamline text concatenation across older versions of Excel.