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.