Text Join Function | How to use Join Text Function in Excel

We have been using the Concatenate Function for a long time in Excel. The Concatenate Function in Excel helps you to join different text strings or numbers. However, the Concatenate Function is replaced by the Text Join Function in Excel 2016. The Concatenate Function was replaced due to the range-

There is no provision for adding delimiters.
Concatenate does not take a limit to perform.
Arguments are separated through a comma.

Due to this limitation, Excel was taking a long time to work with the Concatenate Function. The best option for working faster is by using Excel VBA code.

Text Join Function was introduced in the year 2016. This is one of the latest functions added to Excel. The Text Join Function joins a series of text strings or numbers supplied together. You can specify a Delimiter to be added between text items.

It is categorized in Excel under the String / Text function. This returns the String / Text value.

Note: The Text Join Function has been introduced in 2016 but to use this function you need to be an Office 365 subscriber.

SYNTAX OF TEXT JOIN FUNCTION

=TEXTJOIN(delimiters, ignore empty, text 1, )

There are three Parameters or Arguments which are essentially required in the Text Join Function. Other parameters are optional.

First Parameter: Setting a Delimiter

The delimiter is the character to be inserted between two texts. In our example, we will use a Comma and a Space to separate two Text Strings. Here Comma and Space are delimiters and joining text is called text string. For example, we associate the names of people with their addresses.

Second Parameter: Whether to ignore empty values or not

It makes sense to ignore empty cells because they take up unwanted space in the text string. The Text Join Function parameter gives an option of whether to ignore empty cells. We can either add True or False to this parameter. True if we want to ignore empty cells and False if we do not want to ignore empty cells.

Third Parameter: Text to be joined

The third parameter is to see the text that needs to be joined. You can attach 2 or more Text Strings to this parameter. This text can be an individual cell or range of cells to be included. However, you can enter a maximum of 252 such arguments. Other arguments are optional.

Example of Text Join in Excel

In the example given below, we have the user’s Name, Street, City, Postal Code in Column format. We want to include these in a text string using the Text Join Function in Excel.

In the cell E2, Enter the Text Join formula as = TEXTJOIN (“,”, TRUE, A2: D2) Press Enter and use the Fill Handle Option to fill in all the other cells.

Note: If you do not want to ignore the empty cell, you can enter FALSE in the argument. You will see that a Comma and Space will be added to the result column.

Points to Remember:

The points you need to keep in mind while doing the TEXTJOIN function in Excel are:

This function provides flexibility to join text using cell ranges instead of individual cell references.
If the Text Join Function is more than 32,767 characters, it will return a #VALUE error.
Concat Function and Text Join Function are the same. The only difference is that Concat Function does not accept Delimiter. Text Join Function Delimiter accepts.
When we forget to comma between strings and combine it will come #NULL Error.
When we use an earlier version of Excel that does not support the Text Join Function, the #NAME? An error will come.

So now we can see how easy this function is. It makes our work easier. It also saves time and effort. We can use this Text Join Function for various purposes. In this way, the Text Join Function is useful when working with Excel.