“The best way to pay for a lovely moment is to enjoy it.” [Richard Bach]

## Abstract

The Excel function TEXTJOIN can concatenate cells or ranges with a given delimiter from version Excel 2019 onwards. For Excel versions older than Excel 2019, you can use the user defined function TEXTJOIN presented here.

The good news: When you migrate to Excel 2019 or newer, you do not need to do anything. Excel will automatically use the built-in function.

Please note: I have intentionally not taken care of pathological cases like Delimiter being an array or Ignore_empty having integer values like 0 or 1. Neither have I mimicked the flaws of Excel’s TEXTJOIN. For example, Excel’s TEXTJOIN function cannot deal properly with intersections of non-contiguous ranges like (A1:C3,D4:F6,G7:I9) (A1:B2,C3:D4,E5:F6,G7:H8,I9:J10). As with almost every analogy, you might want to draw a line where it should stop.

## Example of Use - List Missing Values

With the formula =IF(Start<End;TEXTJOIN(",",TRUE,IF(ISNA(MATCH(SEQUENCE(MAX(–List)-MIN(–List)+1,,MIN(–List)),–List,0)),SEQUENCE(MAX(–List)-MIN(–List)+1,,MIN(–List)),"")),"") you can list missing values in the range List with Excel 2021 and Excel 365:

With Excel 2019 please use the formula =IF(Start<End;TEXTJOIN(",",TRUE,IF(ISNA(MATCH(ROW(INDIRECT(MIN(–List)&":"&MAX(–List))),–List,0)),ROW(INDIRECT(MIN(–List)&":"&MAX(–List))),"")),"").

For older versions of Excel than 2019, you will need the user defined function TEXTJOIN given in the Appendix here.

## Appendix – TEXTJOIN Code

``````Option Explicit

Function TEXTJOIN(Delimiter As String, _
Ignore_empty As Boolean, _
ParamArray Text() As Variant) As String
'Source (EN): http://www.sulprobil.com/textjoin_en/
'Source (DE): http://www.bplumhoff.de/textverketten_de/
'(C) (P) by Bernd Plumhoff 07-Jan-2022 PB V1.1
Dim v, i As Long, s As String, t As String
For i = LBound(Text) To UBound(Text)
If IsArray(Text(i)) Then
For Each v In Text(i)
t = IIf(IsMissing(v), "", v)
If Not (Ignore_empty And t = "") Then
TEXTJOIN = TEXTJOIN & s & t
s = Delimiter
End If
Next v
Else
t = IIf(IsMissing(Text(i)), "", Text(i))
If Not (Ignore_empty And t = "") Then
TEXTJOIN = TEXTJOIN & s & t
s = Delimiter
End If
End If
Next i
End Function
``````