Joins multiple text strings with a delimiter. Can ignore empty cells automatically. Essential for combining text data with flexible formatting and delimiter control in Excel.
Master the fundamentals of Excel TEXTJOIN function
The TEXTJOIN function is Excel's advanced text joining tool that combines multiple text strings with a specified delimiter. It supports ranges, can automatically ignore empty cells, and provides flexible delimiter control, making it superior to CONCATENATE for modern Excel workflows.
Built-in delimiter parameter for flexible joining
Can ignore empty cells automatically
Can join entire ranges directly
Supports any delimiter string
Function-specific parameters
Function-specific return type
Create comma-separated values from ranges
Format lists with custom delimiters
Combine data from multiple sources
Generate formatted text reports
Exact matching required
Returns numeric position
Handles missing text gracefully
=TEXTJOIN(delimiter, ignore_empty, text1, text2)Text string to place between each text item
TRUE to ignore empty cells, FALSE to include them
First text string or range to join
Additional text strings or ranges to join
Text string with joined values and delimiter
Description: Joins multiple text strings with a delimiter. Supports ranges and can ignore empty cells.
Join text strings with delimiter
Joins two cells with a space delimiter
Simple VBA implementation of TEXTJOIN function
' Basic TEXTJOIN in VBA
Range("C1").Value = Application.WorksheetFunction.TextJoin(" ", True, Range("A1").Value, Range("B1").Value)
' Using VBA TEXTJOIN function
Dim result As String
result = Application.WorksheetFunction.TextJoin(", ", True, "John", "Doe", "Smith")
' Join range values with delimiter
Sub TextJoinRange()
Dim cell As Range
Dim values() As String
Dim count As Long
count = 0
ReDim values(1 To Range("A1:A10").Cells.Count)
For Each cell In Range("A1:A10")
If Not IsEmpty(cell.Value) Then
count = count + 1
values(count) = CStr(cell.Value)
End If
Next cell
ReDim Preserve values(1 To count)
Range("B1").Value = Join(values, ", ")
End Sub
' Advanced TEXTJOIN with custom delimiter
Sub TextJoinWithDelimiter()
Dim delimiter As String
Dim cell As Range
Dim result As String
Dim firstCell As Boolean
delimiter = " | "
firstCell = True
result = ""
For Each cell In Range("A1:A10")
If Not IsEmpty(cell.Value) Then
If Not firstCell Then
result = result & delimiter
End If
result = result & CStr(cell.Value)
firstCell = False
End If
Next cell
Range("B1").Value = result
End SubCustom VBA function to mimic TEXTJOIN behavior
' Function to join range with delimiter (like TEXTJOIN)
Function TextJoinRange(rng As Range, delimiter As String, ignoreEmpty As Boolean) As String
Dim cell As Range
Dim result As String
Dim firstCell As Boolean
firstCell = True
result = ""
For Each cell In rng
If Not (ignoreEmpty And IsEmpty(cell.Value)) Then
If Not firstCell Then
result = result & delimiter
End If
result = result & CStr(cell.Value)
firstCell = False
End If
Next cell
TextJoinRange = result
End Function
' Usage in worksheet
' =TextJoinRange(A1:A10, ", ", TRUE)
' Join multiple ranges
Function TextJoinMultiple(rng1 As Range, rng2 As Range, delimiter As String) As String
Dim result1 As String
Dim result2 As String
result1 = TextJoinRange(rng1, delimiter, True)
result2 = TextJoinRange(rng2, delimiter, True)
If Len(result1) > 0 And Len(result2) > 0 Then
TextJoinMultiple = result1 & delimiter & result2
Else
TextJoinMultiple = result1 & result2
End If
End FunctionCreate comma-separated values from ranges
Format lists with custom delimiters
Combine data from multiple sources
Generate formatted text reports
Extra delimiters appear when ignore_empty is FALSE
=TEXTJOIN(", ", TRUE, A1:A10)Solution: Set ignore_empty to TRUE to skip empty cells: =TEXTJOIN(", ", TRUE, A1:A10)
Numbers in ranges are treated as text
=TEXTJOIN(", ", TRUE, TEXT(A1:A10, "0"))Solution: This is expected. Use TEXT function to format: =TEXTJOIN(", ", TRUE, TEXT(A1:A10, "0"))
TEXTJOIN may be slow with very large ranges
=TEXTJOIN(", ", TRUE, A1:A10000)Solution: Consider breaking into smaller ranges or using VBA for better performance with large datasets.
Delimiter appears at start or end when range has empty cells
=TRIM(TEXTJOIN(", ", FALSE, A1:A10))Solution: Set ignore_empty to TRUE to prevent leading/trailing delimiters, or use TRIM to remove them.