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.
Argument | Required | Description |
---|---|---|
delimiter | Yes | A 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_empty | Yes | If TRUE, the function will ignore any empty values. |
row_priority | Yes | If 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). |
text1 | Yes | Value 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, …] | No | Additional 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.


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)

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