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.
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)
- 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
The My_Text_Join function will return a string/text value.
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.