Excel TEXTJOIN Function

The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

Syntax:

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

For example

= TEXTJOIN(" ",TRUE, "The", "sun", "rises", "from", "the", "east.") will return The sun rises from the east.

argument	Description
delimiter (required)	A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text.
ignore_empty (required)	If TRUE, ignores empty cells.
text1 (required)	Text item to be joined. A text string, or array of strings, such as a range of cells.
[text2, ...] (optional)	Additional text items to be joined. There can be a maximum of 252 text arguments for the text items, including text1. Each can be a text string, or array of strings, such as a range of cells.

     

Why do we use TEXTJOIN?

• TEXTJOIN is easy compared to concatenate and "&" operator

To combine values in cells A1:A15 with CONCATENATE, we have to write =CONCATENATE(A1, A2, A3, A4, A5, A6, …, A15) or A1&A2&A3&A4&A5&A6…&A15

With TEXTJOIN, you can simply say =TEXTJOIN(,,A1:A15)

• Add delimiters

You can comma separate or add other delimiters when combining text with TEXTJOIN. While you can do the same with CONCATENATE, it becomes doubly annoying. =TEXTJOIN(“, “,,A1:A15) gives you a comma separated text of values in A1:A15  

Remarks

• If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! error.