“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]