Excel TEXTJOIN Function — Join a Whole Range With a Delimiter (and Skip the Blanks)

TL;DR — TEXTJOIN glues a range of values into one string, puts a delimiter between them, and — this is the part everyone misses — skips the empty cells for you. Syntax: =TEXTJOIN(delimiter, ignore_empty, text1, …). The second argument is the whole point: TRUE drops blanks so you never get "Anna, , , Ben"; FALSE keeps the gaps. One ceiling to know: the result can't exceed 32,767 characters (one cell's worth) or you get #VALUE!. Available in Excel 365 and Excel 2019+.

=TEXTJOIN(", ", TRUE, A2:A100)
=TEXTJOIN(" | ", TRUE, FILTER(Email, Status = "Active"))

For years, turning a column of names into "Anna, Ben, Carla" meant a chain of A2 & ", " & A3 & ", " & A4 … that broke the moment a cell was empty (you'd get a stray , ,) or someone inserted a row. TEXTJOIN replaces the whole chain with one expression that takes a range, a separator, and a switch that handles blanks. It's the function you reach for whenever the destination is one cell holding a list.

What you'll learn

• Why ignore_empty — not the delimiter — is the argument that matters
• The 32,767-character ceiling and the #VALUE! it throws
• When TEXTJOIN wins over CONCAT, CONCATENATE, and the & operator
• How to join a spilled result (FILTER, UNIQUE) back into a single cell
• The delimiter trick most people never try: an array of separators

The mental model: a glue gun with a delimiter

The & operator and old CONCATENATE glue two things at a time, and you have to type the separator by hand between every pair. TEXTJOIN is a glue gun: you load it with one delimiter, point it at a whole range, and it lays the separator between each value automatically — and only between them, never trailing on the end. That single behavioural difference is why TEXTJOIN exists and why it reads so much cleaner:

' The old way — separator repeated by hand, breaks on a blank cell:
=A2 & ", " & A3 & ", " & A4 & ", " & A5

' TEXTJOIN — one delimiter, one range:
=TEXTJOIN(", ", TRUE, A2:A5)

The rule that unlocks everything: ignore_empty is the whole point

Here is the number-one "my list is full of commas" complaint, and it's not a delimiter problem — it's the second argument. ignore_empty decides what happens to blank cells:

' A3 and A5 are empty:
=TEXTJOIN(", ", TRUE,  A2:A6)   ' -> "Anna, Ben, Carla"        (blanks skipped)
=TEXTJOIN(", ", FALSE, A2:A6)   ' -> "Anna, , Ben, , Carla"    (blanks kept)

TRUE is what you want 95% of the time: it produces a clean list with no double separators, no leading or trailing comma. FALSE is the rarer, deliberate choice — you keep it when each position has to line up with something else (a fixed-width record, a template where slot 3 must stay slot 3 even when empty). The reason & and CONCATENATE feel so brittle is that they have no equivalent of this switch: every empty cell becomes a visible gap you have to clean up after.

The ceiling nobody mentions: 32,767 characters

A TEXTJOIN result lives in one cell, and a cell holds at most 32,767 characters. Join a long column and you can sail past that limit — at which point Excel returns #VALUE!, not a truncated string. This is the one hard wall on TEXTJOIN:

=TEXTJOIN(",", TRUE, A:A)    ' -> #VALUE! if the joined text exceeds 32,767 chars

If you hit it, you're almost always building the wrong thing — a single cell is rarely the right home for tens of thousands of characters. Either join a bounded range (A2:A500) or rethink whether the data belongs in one cell at all.

Join a spilled array back into one cell

This is where TEXTJOIN earns its place in modern Excel. The dynamic-array functions — FILTER, UNIQUE, SORT — spill their results across many cells. Sometimes you want the opposite: collapse that spill into a single readable string (a summary cell, an email "To" line, a CSV row). TEXTJOIN takes an array directly, so you nest them:

' Every active customer's email, de-duplicated, in one cell:
=TEXTJOIN("; ", TRUE, UNIQUE(FILTER(Email, Status = "Active")))

No spill range to manage, no helper column — the inner functions produce the array, TEXTJOIN flattens it. The delimiter can even be an array of separators ({", "; " and "}) if you want the last item joined differently, a trick almost no tutorial shows.

The judgment call: TEXTJOIN, CONCAT, or &?

Use & only for gluing two or three known things (First & " " & Last). The moment you're joining a range or a list, the question is just: do you need a separator or to skip blanks? If yes — and it almost always is — use TEXTJOIN. Reach for CONCAT only when you genuinely want no delimiter and no blank-skipping; the second you find yourself adding & or SUBSTITUTE to fake a separator on top of CONCAT, you wanted TEXTJOIN from the start.

How ExcelMaster helps

A lot of TEXTJOIN work is really "turn this column into a string I can paste somewhere else": the email addresses of everyone overdue, semicolon-separated. ExcelMaster writes that formula from a plain-English description — including the ignore_empty switch and any FILTER/UNIQUE it needs to feed the join — and drops the result where you want it. You describe the list; it handles the join.

Frequently asked questions

How do I combine cells with a comma in Excel?

Use =TEXTJOIN(", ", TRUE, A2:A100). The first argument is the separator, the second (TRUE) skips empty cells, and the third is the range. The comma and space appear only between values, never at the end.

How do I make TEXTJOIN ignore blank cells?

Set the second argument to TRUE: =TEXTJOIN(", ", TRUE, range). With FALSE, every empty cell becomes an empty slot and you get doubled-up separators like "a, , b".

Why does TEXTJOIN return #VALUE!?

The most common cause is the 32,767-character limit — a cell can't hold more than that, so a join of too many values overflows. Join a smaller range, or reconsider whether the data should live in a single cell.

TEXTJOIN vs CONCATENATE — what's the difference?

CONCATENATE (and &) glue values with no separator and no way to skip blanks, and CONCATENATE can't take a whole range — you list cells one by one. TEXTJOIN takes a range or array, inserts a delimiter automatically, and skips empties on request. For lists, TEXTJOIN wins every time.

Does TEXTJOIN work in Excel 2016?

No. TEXTJOIN arrived in Excel 2019 and Excel 365. On 2016 you fall back to & or CONCATENATE with manual separators.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-17.

Related guides: Excel TEXTSPLIT · Excel CONCAT · Excel FILTER · VBA Concatenate