How to use TEXTJOIN function in Excel?

This post explains how to use TEXTJOIN function in Excel. TEXTJOIN is a Text function in Excel that joins (or concatenates) text strings from two or more cells and separates the combined value using a delimiter. It is a more powerful alternative to the concatenation operator (&) or the CONCATENATE function, which are two other ways of concatenating strings in Excel. TEXTJOIN function is available in Excel for Office 365, Excel 2019, and Excel 2021.

The syntax of the TEXTJOIN function is as follows:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Where,

delimiter is the character that separates the concatenated values. This is a required argument. It takes a text string enclosed within double quotes or a reference to a text string as its value. Comma (“,”), space (” “), and hyphen (“-“) are some of the examples used as delimiters in the TEXTJOIN function. An empty string (“”) may be specified to use no delimiter.
ignore_empty takes either TRUE or False, where TRUE is the default value. It tells Excel whether to include or ignore empty cells in the resultant string. False keeps the empty values and True ignores them. This is also a required argument.
text1 takes the main string value that needs to be merged. This argument is also required. It can be a text string, a reference to the cell containing the text string, or an array of strings (a reference to a cell range).
[text2], … are additional (and optional) text values that need to be merged with the main string. You may join up to 252 strings together, including text1.

How to use TEXTJOIN function in Excel?

Let us see how to use the TEXTJOIN function in Excel.

Example 1: Using TEXTJOIN using a single delimiter

Suppose we have a workbook with employee data (employee names) of an organization, as shown in the above image. Column A contains the first names of the employees, column B contains middle names of the employees, and column C contains last names of the employees. To join these values as full names, we can use the TEXTJOIN function in cell D3 as:

=TEXTJOIN(" ", TRUE, A3:C3)

For the first row (Row 3), the function will return ‘Laxman+” “+(empty_value)+” “+Singh’

Since the ignore_empty argument is set to TRUE, it will ignore any empty values within the specified cell range. So the final result will be:

‘Laxman+” “+Singh’, which becomes ‘Laxman Singh’, as shown in the above image.

On the other hand, if we set ignore_empty as FALSE, the function will return ‘Laxman Singh’, which includes 2 spaces, taking into consideration the empty value as well.

Tip: To repeat the same formula for all the rows, you can take your mouse pointer to the bottom-right corner of cell D3 (which contains the TEXTJOIN formula), and as the pointer turns into plus (+) sign, click and then drag the pointer till cell D6. The same formula will apply to cells D4 through D6 when you release the mouse pointer.

Example 2: Using TEXTJOIN using multiple delimiters

Now suppose we have another datasheet where Column A contains last names of the employees, column B contains first names of the employees, and column C contains middle names of the employees of an organization, as shown in the above image. If we want the full names in the format Last name,First name Middle name, we may use the TEXTJOIN function as follows:

=TEXTJOIN({","," "}, TRUE, A3:C3)

Here, we have supplied more than 1 delimiter (comma and space) to join the string values. So the function will pick cell value A3 (Singh), concatenate it with a comma character(Singh+”,”), then pick cell value B3 and concatenate it with space character(Singh+”,”+Laxman+” “), and then concatenate this entire value with the value of cell C3(Singh+”,”+Laxman+” “+””). The results will now appear in the desired format(Singh,Laxman ), as shown in the above image.

Notes:

If the length of the resultant strings exceeds 32767 characters (which is the limit of a cell in Excel), the TEXTJOIN function will return #VALUE! error.
If delimiter argument is not supplied or is empty,(“”), the function will join the cell values without a delimiter.
To merge date values with string values, you need to first convert the date into text format using the TEXT function.
To insert line break between string values, use CHAR(10) as the delimiter and turn on the ‘Wrap Text’ format for the cell. 10 is the ASCII character code for linefeed.
A number supplied as delimiter or text in the TEXTJOIN function is converted to text during the concatenation process.

This is how you can use the TEXTJOIN function to combine data from a range of cells. If you need to reverse the changes, you can use the TEXTSPLIT function to split the string on the basis of given delimiters.

Hope you find this useful.

Why is my TEXTJOIN formula not working?

The TEXTJOIN formula may not be working on your system if you’re having a version of Excel that doesn’t support this formula. TEXTJOIN is only available in Excel 2019 and later. You can also use it if you use Excel for Office 365. On any other version (Excel 2016 or earlier), it will show the #NAME? error.

How do I combine two text cells in Excel?

You can combine two text cells in Excel using the TEXTJOIN function. No matter whether you want to join two or more than two cells, you can use this function to get the job done. A detailed guide on TEXTJOIN function is mentioned here and it is recommended to follow it.