Combining text from multiple cells is a common requirement in Excel. Many users use the CONCATENATE or CONCAT function. However neither of these allows you to include a character or characters (known as delimiters) between each item. If that’s what you need to do, the TEXTJOIN function is the perfect solution.
Take the screenshot above as an example. I need to combine the text in A1, B1 and C1 into a single string (in E1) with comma and space between each item.
The formula in E1 is =TEXTJOIN(“, ” , TRUE, A1:C1)
TEXTJOIN can not only use traditional characters as delimiters (letters, numbers and non-alphanumeric characters such as commas, hyphens and so on) it can also use “special” delimiters such as line breaks and tabs – characters that you can’t type directly into the function.
In the screenshot above, the text in A7 is the result of combining text from 5 cells (A7:E7) and specifying a line break as the delimiter. To use a line break as a delimiter enter CHAR(10) as the first argument
The formula in A7 is =TEXTJOIN(CHAR(10), TRUE, A7:E7)
Another benefit of TEXTJOIN is that it can ignore empty cells, meaning that you don’t end up with additional spaces in your combined text string.
In the screenshot above cell B2 is empty resulting in an extra comma and space in E2. Using TRUE as the second argument is how you do that.
The formula in E2 is =TEXTJOIN(“, ” , TRUE, A2:C2)
In this video I’ll show you several cool tricks using the TEXTJOIN function…
- I’ll combine the values from multiple cells into a single cell and have each item on a separate line.
- I’ll combine the values from multiple cells into a single cell and have each item separated with a tab.
- I’ll show you how to use multiple delimiters in the same formula.
Formulas used in the video:
- =TEXTJOIN(” “,TRUE,A2:C2)
- =TEXTJOIN(CHAR(10),TRUE,’TEXTJOIN 3a’!A2:F2)
- =TEXTJOIN(CHAR(9),FALSE,A2:D2)
- =TEXTJOIN(” “,TRUE,A2:C2) & “, ” & TEXTJOIN(“, “,TRUE,D2:E2)
Download a copy of the file(s) used in this video: https://share.zight.com/v1uGdboG