Join several cells with a separator

Why it's built this way

• →The second argument, ignore_empty, is the reason to reach for TEXTJOIN over CONCATENATE or &: set it TRUE and a blank cell in the range is skipped entirely, instead of leaving a bare delimiter sitting where it was.
• →TEXTJOIN accepts a whole range as its third argument, not a cell-by-cell list — CONCATENATE and & both require every cell spelled out individually.
• →The delimiter can be more than one character, so the joined text reads naturally instead of needing a cleanup pass afterward.

The fine print

• →TEXTJOIN was added in Excel 2019/365 and Google Sheets, but isn't available in Excel 2016 or earlier — CONCATENATE or & is the fallback there.
• →The delimiter only appears BETWEEN values, never at the start or end, so joining a single non-empty cell produces no stray separator.
• →ignore_empty must be a boolean (TRUE/FALSE) or 1/0 — typing the word "true" as literal text is not the same thing and errors.

Part by part

=TEXTJOIN(

Joins a range with a separator between each value. The separator is written once rather than repeated between every argument, which is the whole advantage over CONCATENATE.

{{delimiter}}

The text placed between values. A literal such as ", " needs quotes; CHAR(10) gives a line break, provided the cell has wrapping enabled.

{{ignore_empty}}

TRUE skips blank cells, FALSE keeps them and produces consecutive separators. This argument is why TEXTJOIN replaced the older functions.

{{range}}

What to join. A range works directly, which CONCATENATE could never do — it required each cell as a separate argument.

Common mistakes

• =TEXTJOIN(", ", FALSE, A2:A10)

  Keeping empty cells produces runs of separators such as "a, , , b" whenever the range has gaps. TRUE is nearly always what is meant, and the argument is easy to pass without thinking.

  =TEXTJOIN(", ", TRUE, A2:A10)
• =CONCATENATE(A2, ", ", A3, ", ", A4)

  Requires writing the separator between every pair, does not accept a range, and breaks the moment a row is inserted. It survives only for compatibility with very old workbooks.

• =CONCAT(A2:A10)

  CONCAT accepts a range and offers no separator at all, so values run together. It is the right function only when you genuinely want them joined with nothing between.

Questions