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


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.

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