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
- From the
Developer
ribbon in Excel, clickVisual Basic
. - In the
Visual Basic for Applications
window, go toInsert
>Module
. - 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): IfTRUE
, the function skips empty values.row_priority
(required): IfTRUE
, joins values by row (left to right). IfFALSE
, 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.