TEXTJOIN Function was introduced with the release of Excel 2016. This is very helpful function to join the multiple text into one. The best thing is that it allows us to select an entire range of cell references to be joined. So it is very time saving formula. But the problem is this formula is not available in lower versions of excel as 2013,2010 or in 2007 etc.
Here I have tried to solve this problem with a UDF (User Defined Functions). I have create a function in VBA which will give the almost same result as TEXTJOIN returns.
As you are seeing in below given image on column A Employee Id list is available and on range C2 a formula “=My_Text_Join(“;”,1,A2:A15)” has been put to get the semicolon separated employee ids in a single cell.
My_Text_Join() is a user defined function. To create this function below is the code. To use this code below are the steps-
- Copy the below given code
- Open a new workbook or you can use your existing workbook also.
- Go to Visual Basic Editor (Press Alt+F11)
- Insert a new module (Press Alt+I+M)
- Paste this code.
- Save workbook as macro enabled.
- Go to the worksheet and type My_Text_Join and use it as normal excel function.
Option Explicit
Function My_Text_Join(delimiter As String, ignore_empty As Boolean, text_range As Range) As String
Application.Volatile
Dim c As Range
Dim n As Long
n = 0
For Each c In text_range
If ignore_empty = True Then
If VBA.IsEmpty(c.Value) = False Then
If n = 0 Then
My_Text_Join = c.Value
Else
My_Text_Join = My_Text_Join & delimiter & c.Value
End If
n = n + 1
End If
Else
If n = 0 Then
My_Text_Join = c.Value
Else
My_Text_Join = My_Text_Join & delimiter & c.Value
End If
n = n + 1
End If
Next
End Function
Syntax of My_Text_Join
My_Text_Join(delimiter, ignore_empty, text_range)
Parameter Details:
- delimiter : A text/character inserted between each text value in the resulting text. Most commonly, you would use a delimiter such as a comma or space character.
- ignore_empty : Determines whether empty values are included in the resulting string. TRUE ignores empty values and FALSE includes empty values in the result. You can also use 1 and o in place of TRUE
- Text_Range : The excel range of strings that you wish to join together
Returns
The My_Text_Join function will return a string/text value.
Examples:
Let’s say we have month name list and we have to make it comma separated list. Month name is available on range “A1:A12”. Put the formula “My_Text_Join(“,”,1,A1:a12)” in any cell.
If we will remove some months name from the list then it take the only months which are available in the list. Because we have used 1 (TRUE) for ignore_empty so it will not give any extra comma.
If we use 0 in place of 1 in the formula “My_Text_Join(“,”,0,A1:a12)” then it will return extra commas.
