Replicating TEXTJOIN using VBA

Formulas, VBA

Excel 2016 introduced the TEXTJOIN function which is a much improved version of CONCATENATE function.

I always found CONCATENATE quite useless for 2 reasons

  1. CONCATENATE did not work with range of cells so CONCATENATE(Rangeofcells) returns the first cell in the range only. See the image below

2. Whatever CONCATENTATE does can also be done using the ampersand i.e. “&” operator in Excel.
So CONCATENATE(A1,B1) and =A1&B1 both do the same job

In Excel 2016, we have TEXTJOIN function which not only allows you to combine a range of cells but also allows you to specify

  1. Delimiter(s) between each text value and
  2. a choice between Include Empty Cells or Exclude empty cells

You will the documentation of this function in the link below.

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

Now back to main purpose of this post. TEXTJOIN function is not available  to Office 365 subscribers.

For those who do not have Office 365 subscription, but still want to use the TEXTJOIN function I have created the following User Defined Function which will serve the same purpose

Function Text_Joined(Delimiter As Variant, IgnoreEmptyCells As Boolean, TextRange As Range) As Variant
Dim textarray()
If IgnoreEmptyCells = True Then
    For i = 1 To TextRange.Cells.Count
        If TextRange.Cells(i) <> "" Then
            k = k + 1
            ReDim Preserve textarray(1 To k)
            textarray(k) = TextRange.Cells(i)
        End If
    Next i
Else
    For i = 1 To TextRange.Cells.Count
        k = k + 1
        ReDim Preserve textarray(1 To k)
        textarray(k) = TextRange.Cells(i)
    Next i
End If
'Now Join the Cells
If Not TypeName(Delimiter) = "Range" Then
    Text_Joined = textarray(1)
        For i = 2 To UBound(textarray) - 1
        Text_Joined = Text_Joined & Delimiter & textarray(i)
        Next i
    If i > 1 Then Text_Joined = Text_Joined & Delimiter & textarray(UBound(textarray))
Else
   Text_Joined = textarray(1)
        For i = 2 To UBound(textarray) - 1
            l = l + 1
            If l = Delimiter.Cells.Count + 1 Then l = 1
        Text_Joined = Text_Joined & Delimiter.Cells(l) & textarray(i)
        Next i
    If i > 1 Then Text_Joined = Text_Joined & Delimiter.Cells(l + i) & textarray(UBound(textarray))
End If
End Function