CONCATENATE vs TEXTJOIN: Which Way to Combine Text

Excel & Google Sheets

How it works

TEXTJOIN was built for exactly this job: give it the delimiter once, tell it whether to skip empty cells, and hand it a whole range. With TRUE in the second argument, the blank row disappears cleanly — "Ana, Ben, Cara" — while FALSE keeps a slot for it and produces the double-comma artifact. CONCATENATE predates both ideas: it has no delimiter argument and can't accept a range as a list, so joining four cells with commas means writing every piece by hand — =CONCATENATE(A2,", ",A3,", ",A4,", ",A5) — and blanks still leave stray separators. TEXTJOIN needs Excel 2019/365 or Google Sheets; that version requirement is the only reason CONCATENATE (or the & operator) still appears in new formulas.

", "

The delimiter, written once — a comma-space here, or CHAR(10) for line breaks.

TRUE

Skip empty cells, so blanks don't leave double commas. FALSE keeps them.

A2:A5

The range to join — no typing each cell. CONCATENATE can't do this.

When to use it

Use TEXTJOIN for attendee lists, address lines from optional parts (unit, street, city), tag lists, and email strings — anywhere the source column has gaps or more than three pieces.

Common mistakes

• ✕Passing a range to CONCATENATE.

  ✓=CONCATENATE(A2:A5) doesn't join the list the way you'd hope — CONCATENATE wants individual pieces. Use =TEXTJOIN(", ",TRUE,A2:A5), or list each cell separately if you're stuck on old Excel.

• ✕Forgetting the second argument and getting stray commas.

  ✓=TEXTJOIN(", ",FALSE,A2:A5) keeps a slot for every blank: "Ana, Ben, , Cara". Set it to TRUE to skip empties.

• ✕TEXTJOIN showing #NAME? for a colleague.

  ✓TEXTJOIN needs Excel 2019/365 or Google Sheets — Excel 2016 and older doesn't have it. For files that circulate widely, fall back to & with manual separators.