TEXTJOIN

Arguments

• delimiter - The separator placed between items, e.g. ", ".
• ignore_empty - TRUE to skip empty cells, FALSE to keep them.
• text1 - The first text item or range to join.

Examples

How TEXTJOIN evaluates

TEXTJOIN is the only join function that puts a separator between items rather than making you type it repeatedly. Its three-part signature - delimiter, ignore_empty, then the things to join - solves the two problems that make & chains painful: a range can be passed as a single argument instead of being spelled out cell by cell, and blank cells can be dropped so the result never contains a run of orphaned commas.

The ignore_empty flag is the argument that decides the shape of the output. With TRUE, empty cells vanish and exactly one delimiter appears between each surviving value; a formula returning "" is dropped the same way, since the empty string counts as empty here. With FALSE, every cell contributes a slot, so a blank produces an empty entry and the delimiters bunch together - which is occasionally what you want, for instance when the result must line up positionally with a fixed set of fields.

TEXTJOIN was added in Excel 2019 and Microsoft 365; it does not exist in Excel 2016 or earlier, where it returns #NAME? and has to be replaced by a & chain or CONCATENATE. Two other limits are worth knowing before you build a report on it. The result is text and is capped at 32,767 characters - the maximum a cell can hold - and exceeding that returns #VALUE! rather than a truncated string. And joined numbers lose their formatting: a cell displaying 1,250.00 or 15/03/2024 arrives as 1250 or 45366, so wrap it in TEXT() when the formatting matters.

Delimiter

Any text, including "" for no separator and CHAR(10) for a line break - which only shows as a line break if the cell has Wrap Text on. In Excel 365 the delimiter may itself be an array, in which case the separators cycle through it between items.

ignore_empty

TRUE drops empty cells and formulas returning "". FALSE keeps a slot for each, producing consecutive delimiters. A blank cell is never rendered as the word blank or as zero.

Ranges and reading order

Each text argument can be a range or an array. A multi-column range is read left to right within each row, then down - not column by column.

Numbers and dates

Converted to their underlying value as text, ignoring the cell's number format. Dates become serial numbers. Use TEXT(A2, "dd/mm/yyyy") inside the join to keep them readable.

Errors

Any error value among the joined cells becomes the result of the whole formula. There is no skip-errors option; wrap the source in IFERROR first.

Length limit

A result longer than 32,767 characters returns #VALUE!. Joining a long column of comments is the usual way to hit it.

Errors TEXTJOIN returns

More examples

Compatibility

Does TEXTJOIN Work in Google Sheets?

Yes. TEXTJOIN uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.

In the ExcelTool editor

The ExcelTool editor implements TEXTJOIN with the standard three-or-more arguments and flattens every text argument, so ranges can be passed directly rather than cell by cell.

The editor requires the delimiter to be a single value: a multi-cell range in that first position returns #VALUE! rather than cycling through several separators as Excel 365 does. The ignore_empty flag is read loosely - TRUE, FALSE, 1, 0 and the text "TRUE" or "FALSE" are all accepted.

With ignore_empty on, both genuinely empty cells and values that are the empty string are skipped, and an error value found anywhere among the joined values is returned as the result of the formula.

The editor does not enforce Excel's 32,767-character cell limit on the joined result, so a very large join that would fail in Excel succeeds here; check the length with LEN before relying on the output in a real workbook.

You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.