TEXTJOIN
Joins text from multiple ranges with a delimiter, with option to ignore empty cells.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)Arguments
delimiterSeparator between valuesignore_emptyTRUE to skip empty cellstext1First text or rangetext2Additional text/ranges(optional)
=TEXTJOIN(", ",TRUE,A1:A5)Joins with comma
=TEXTJOIN("-",TRUE,A:A)Joins column with hyphens
=TEXTJOIN(CHAR(10),TRUE,A1:A3)Joins with line breaks
- •Much better than nested CONCATENATE
- •Can join entire ranges at once
- •Excel 2019 and later
- •Building a formula around TEXTJOIN for a workbook that still needs to open in Excel 2016 - it simply isn't there
- •Setting ignore_empty to FALSE and ending up with a trail of extra delimiters where blank cells used to be
- •Using CHAR(10) to force line breaks and then forgetting to turn on Wrap Text, so the breaks are there but invisible
CONCATENATEThe older function TEXTJOIN was built to replace for joining ranges with a delimiterTEXTFormat numbers or dates with this first so they read correctly once joined togetherSUBSTITUTEGood for tidying up the joined result afterward, like fixing doubled-up delimitersWhich Excel versions actually have TEXTJOIN?
Excel 2019, Excel 2021, and Microsoft 365. Nothing older - Excel 2016 and earlier need CONCATENATE or the & operator instead.
How do I skip blank cells when joining a range?
Set ignore_empty to TRUE, like TEXTJOIN(", ",TRUE,A1:A10). Skip this and every blank cell in the range leaves a stray delimiter behind.
Can TEXTJOIN put each value on its own line inside one cell?
Yes, use CHAR(10) as the delimiter, e.g. TEXTJOIN(CHAR(10),TRUE,A1:A5), then turn on Wrap Text for the cell - otherwise the line breaks are there but you won't see them.
Need to translate a formula using TEXTJOIN?
Use our translator to convert your complete formula