**“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.

Good thing is: 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 intentionally have 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 each and 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 range *List* with Excel 2021 and Excel 365:

With Excel 2019 please use 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

Please read my Disclaimer.

```
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
```

## Download

Please read my Disclaimer.

Textjoin.xlsx [13 KB Excel file, open and use at your own risk]

In case you are using an older version of Excel than 2019 (the blue output area will show error values, of course):

Textjoin_with_UDF.xlsm [18 KB Excel file, open and use at your own risk]