How to Use the Excel TEXTJOIN Function

Are you looking to improve your proficiency in Excel formulas and functions? Here is a new one for you: The Excel TEXTJOIN function is used to join text strings into one string. It is often the solution when text that we would like to see appearing together is located in different places or across several cells.

TEXTJOIN is available if you have Excel 2016 or later. Before these versions, we used solutions like CONCATENATE or the ampersand symbol (&) to combine text in Excel from multiple cells into a single cell. So what has changed?

TEXTJOIN overview

The TEXTJOIN syntax is as follows:

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

Argument definitions

The delimiter is the character or characters which will separate each argument. This should be entered within double quotes. If you do not want a delimiter between text strings, simply type a comma to move on to the second argument.

Ignore_empty  is a setting that tells Excel what to do if it finds empty cells in the elements to be joined. If this argument is set to TRUE, empty cells will be skipped without a delimiter indicating an empty cell. If this argument is set to FALSE, the delimiter is returned anyway, so you will have multiple delimiters in a row.

The text1, text2, and subsequent arguments make reference to text strings or cell references with the strings to be joined. TEXTJOIN accepts cell ranges as text arguments. Only the first text argument is required.

• Up to 252 text arguments may be entered. 
• If TEXTJOIN results in more than 32,767 characters, a #VALUE! error will be returned. 

In the following example, TEXTJOIN merges the elements of each phone number from multiple cells quite easily and elegantly.

=TEXTJOIN(“-”,TRUE,A2:C2)

Why use TEXTJOIN? 

1. TEXTJOIN simplifies the process to join text strings. The use of the delimiter argument when a character is to be repeated between each element reduces the number of keystrokes formerly used with CONCATENATE and the ampersand symbol (&), as each delimiter has to be entered manually with those methods.
2. With CONCAT, CONCATENATE, and the ampersand, there is no quick way to designate a character to be repeated between each text string item.
3. The ability to use a range as a single argument is also a great time-saving feature. 

When delimiters are not identical

If your situation is one where delimiters are unique or inconsistent, another approach is required. 

In the following data set, address elements are typically separated by a mixture of spaces and dashes.

If TEXTJOIN is used, the delimiter argument is skipped, and each element is manually combined by using the desired delimiter.

=TEXTJOIN(,,A2," ",B2,", ",C2,", ",D2," ",E2)

Each element in the address is separated by a mixture of spaces and commas with spaces. Note that we also skipped the ignore_empty argument by typing a comma to move on to the text arguments. Using this format makes TEXTJOIN behave in almost the same way as the CONCAT function (also new with Excel 2016).

Cool tricks with TEXTJOIN

Insert a line break using TEXTJOIN

In the following example, each customer’s title (if any), first name, last name, and the elements of their address are to be joined, each separated by a space. 

If we also want to insert line breaks after the name and street address elements, the CHAR(10) unicode character is entered after those references. 

=TEXTJOIN(" ",TRUE,A2:C2,CHAR(10),D2:E2,CHAR(10),F2:H2)

Note that:

• The second argument is set to TRUE so that if Excel comes across any empty cells, it does not create an extra space in the output cell. Column I has been set to wrap text so that the result of the line break is displayed.
• The blank line in Row 5 is the result of the CHAR(10) character after Column E and is not due to the empty cells in D5 and D5.

Though ranges are also permitted with the CONCAT function, it provides no advantage here since delimiters are required between each element. With the CONCAT function, the entry would be:

=CONCAT(A2," ",B2," ",C2,CHAR(10),D2," ",E2," ",CHAR(10),F2," ",G2," ",H2)

That would mean a total of 16 arguments using CONCAT, compared with 7 arguments in TEXTJOIN.

Furthermore, CONCAT cannot ignore empty cells so the TRIM function would have to be nested around the formula to get the same result as TEXTJOIN.

=TRIM(CONCAT(A2," ",B2," ",C2,CHAR(10),D2," ",E2," ",CHAR(10),F2," ",G2," ",H2))

The ignore_empty argument in TEXTJOIN handles this perfectly without any extra steps, so that unnecessary delimiters are removed from empty cells.

Dynamic output

With the arrival of Microsoft 365 comes the exciting feature of dynamic arrays and dynamic spilling. This topic is not covered in detail here, but it is a game-changer in the way existing functions behave.

For example, prior to functions with dynamic behavior, there would have been no simple way to list all the members of each animal group without nesting several functions as shown below. (The Formula Bar has been expanded to show the formula in its entirety.)

This method is complex and can easily lead to errors and frustration.

Compare this with TEXTJOIN in the modern Excel 365 environment, where the entry is easy to build and to follow.

=TEXTJOIN(", ",TRUE,IF(B2:B12=D2,A2:A12,""))

The inner portion of the formula IF($B$2:$B$12=D2,$A$2:$A$12,"") asks Excel to compare each value in the B2:B12 range with the value in cell D2. If there is a match, it should then return the corresponding value in Column A. If there is no match, then nothing will be returned. Since Excel can now process both arrays and return their results as a spilled array, all the matches are stored in Excel’s memory. See below for what Excel does with the IF function.

The TEXTJOIN is then wrapped around that formula to join these results into a single text string, separated by a common delimiter.

=TEXTJOIN(", ",TRUE,IF($B$2:$B$12=D2,$A$2:$A$12,""))

The formula can be copied to the cells below, with the absolute references making sure that the ranges stay fixed, and the relative reference to cell D2 updating for rows 3, 4, and 5.

 Pretty amazing.

Troubleshooting

Check out the following if your TEXTJOIN function is not working:

“TEXTJOIN is not in my list of available functions”

That’s too bad; it’s likely because you are on a pre-2016 version of Microsoft Excel. A great workaround, however, is that it is available on the free Excel Online version. Bear in mind that files with TEXTJOIN formulas will not work if you open them in an older version of Excel where that formula does not exist.

“I set ignore_empty to TRUE but I’m still getting blanks”

The main reason for setting ignore_empty argument to TRUE is to create a uniform appearance so that your merged values do not appear to have missing data, or have unnecessary delimiters. If you use TEXTJOIN and a blank shows up, this is most likely because there are spaces in cells that appear to be empty. Rather than going through each cell one by one to correct this problem, try combining TEXTJOIN with the TRIM function.

The TRIM function removes spaces from text except for single spaces between words. Its syntax is:

TRIM(text)

The idea is to remove any unintended spaces from the text being merged before attempting to combine them. 

=TEXTJOIN("-",TRUE,TRIM(A3:D3))

By placing TRIM in the inner portion of the formula, Excel completes that task before applying TEXTJOIN to the range.

“My currency symbol got dropped after using TEXTJOIN”

If your original text had the Currency or Accounting format applied, the currency symbol is not a part of the characters in the cell. The symbol only appears because this is the display that comes with that particular number format. TEXTJOIN is a text function. Therefore, it converts all values to Text format.

If you want the currency symbol to appear in the output cell, it will have to be treated as a separate delimiter. In this case, it is probably a better idea to use the CONCAT function and enter the text values and delimiters one by one. Of course, TEXTJOIN can still be used, but it will be a few more keystrokes in this case.

=CONCAT(A2,"--> ",B2,"--> ","$",C2)

or 

=TEXTJOIN(,TRUE,A2,"--> ",B2,"--> ","$",C2)

The future of concatenation in Excel

The old favorite, CONCATENATE, is still available for backward compatibility for now, but is being phased out in favor of TEXTJOIN and CONCAT. Hopefully, you’re now comfortable enough with TEXTJOIN to ditch CONCATENATE, since it can do everything you did before and a whole lot more.

In conclusion, the TEXTJOIN function in Excel provides a simple and efficient solution for joining multiple text strings into one string, and offers a number of advantages over previous methods like CONCATENATE and the ampersand symbol (&). With TEXTJOIN, you can easily handle situations where delimiters are inconsistent or unique, insert line breaks, and create dynamic outputs with spilled arrays.

If you are using Excel 2016 or later, it is highly recommended to take advantage of this powerful function. So why not check out the GoSkills Microsoft Excel resources to learn more about TEXTJOIN and other essential resources for data analysis in Excel? Mastering these skills will not only boost your productivity but also make you a more valuable asset in today's job market.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial