TEXTJOIN function
TEXTJOIN joins many values or whole ranges into one string with a separator you choose, and can skip blanks. It is the go-to for building comma-separated lists, with output capped at 32,767 characters.
Example
Read it as a sentence: join the values in F1:F4 with hyphens, skipping blank cells, which gives 123-45351-TRUE.
Excel shows 123-45351-TRUE
F
1
2
'123
3
2024-02-29
4
TRUE
Data
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Arguments in [square brackets] are optional.
-
delimiter(required) - Text to put between items.
-
ignore_empty(required) - TRUE to skip blank cells.
-
text1(required) - First item or range.
-
text2(optional) - More items or ranges.
Returns: One joined string with delimiters.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=TEXTJOIN(", ",TRUE,"a","","b") | a, b | Ignore empty |
=TEXTJOIN(", ",FALSE,"a","","b") | a, , b | Keep empty |
=TEXTJOIN({"-","+"},TRUE,1,2,3) | 1-2+3 | Delimiter array |
=TEXTJOIN(,TRUE,"a","b") | ab | Omitted delimiter |
=TEXTJOIN("-",TRUE,Data!F1:F4) | 123-45351-TRUE | Blank, text number, date, bool |
=TEXTJOIN("-",FALSE,Data!F1:F2) | -123 | Blank kept |
=TEXTJOIN(",",TRUE,REPT("a",20000),REPT("b",20000)) | #CALC! | Result over 32767 |
=TEXTJOIN("-",TRUE,Data!F5) | #DIV/0! | Error |
The Data sheet these formulas read
A cell that starts with = holds that formula.
F
1
2
'123
3
2024-02-29
4
TRUE
5
=1/0
Data
Watch out for
- Output is capped at 32,767 characters.
- Setting ignore_empty to FALSE leaves double delimiters for blanks.
Where it works
- Excel
- TEXTJOIN arrived in Excel 2019. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .