Text-Join-UDF
VBA

Text Join Formula for all Excel Versions

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

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.

Convert Month Name list to comma separated
Convert Month Name list to comma separated

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.

Blanks has been ignored
Blanks has been ignored

If we use 0 in place of 1 in the formula “My_Text_Join(“,”,0,A1:a12)” then it will return extra commas.

Blanks has not been ignored
Blanks has not been ignored

Click here to download this excel file

Watch the Video tutorial:

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply

Your email address will not be published. Required fields are marked *