Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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.

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 .