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.