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

Appendix – TEXTJOIN Code

Please read my Disclaimer.

Option Explicit

Function TEXTVERKETTEN(Trennzeichen As String, _
    Leer_ignorieren As Boolean, _
    ParamArray Text() As Variant) As String
'Source (EN): http://www.sulprobil.de/textjoin_en/
'Source (DE): http://www.berndplumhoff.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 (Leer_ignorieren And t = "") Then
        TEXTVERKETTEN = TEXTVERKETTEN & s & t
        s = Trennzeichen
      End If
    Next v
  Else
    t = IIf(IsMissing(Text(i)), "", Text(i))
    If Not (Leer_ignorieren And t = "") Then
      TEXTVERKETTEN = TEXTVERKETTEN & s & t
      s = Trennzeichen
    End If
  End If
Next i
End Function