Excel TEXTJOIN: ignore empty cells the right way

You export a list of tags, addresses, or line items to one cell and the result reads like this: red, , blue, , , green. Empty cells in the source range become doubled commas in the output. TEXTJOIN was built to solve exactly that, but the ignore_empty switch has one blind spot that keeps burning people. This walks through the argument that fixes 90% of the mess, the one it silently leaves alone, and the FILTER-style patterns that make TEXTJOIN worth reaching for instead of chaining & operators.

What TEXTJOIN actually does

TEXTJOIN takes a range, joins every non-blank cell into a single string, and puts a delimiter of your choice between each value. It’s the modern replacement for the old ampersand-chain (A2&", "&B2&", "&C2) that everyone rewrites when a fourth column appears.

=TEXTJOIN(", ", TRUE, A2:A20)

The signature is TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...). delimiter is any string — a comma, a newline, an em dash, or an empty "" to concatenate with nothing between values. ignore_empty is a Boolean. text1 onward are the ranges or literal strings to join; you can pass up to 252 of them, though a single range argument covers most real use cases.

The two functions Excel ships next to it look similar and are not:

ignore_empty and the trap it doesn’t catch

Set ignore_empty to TRUE and TEXTJOIN skips truly blank cells before writing the delimiter — no doubled commas, no trailing separator. That’s the argument that cleans up the exported-list problem in one keystroke.

=TEXTJOIN(", ", FALSE, A2:A6)
red, , blue, , green

=TEXTJOIN(", ", TRUE, A2:A6)
red, blue, green

Here’s the trap. ignore_empty checks whether the cell is empty, not whether it is visually blank. A cell that holds a single space, a stray tab, or the string "" returned from a formula is not empty to Excel — it contains a value. TEXTJOIN will happily join that value and put a delimiter around it, and your output gets a phantom entry that looks like an extra comma with nothing after it.

Cleaning the input first

The fix is to strip the whitespace before TEXTJOIN sees the cell. Wrap the range in TRIM inside an array-friendly context, or filter the range down to non-whitespace values. On dynamic-array Excel (Microsoft 365 or 2021 and up), this is a one-liner:

=TEXTJOIN(", ", TRUE, IF(TRIM(A2:A20)="", "", TRIM(A2:A20)))

The inner IF rewrites any whitespace-only cell as truly empty, and ignore_empty then does what you expected the first time. Everything else keeps its trimmed value.

Join a filtered subset without a helper column

The most useful TEXTJOIN pattern isn’t joining a whole column — it’s joining just the rows that meet a condition. Say you have a table of orders and you want a single cell listing every product a specific customer bought. The pre-2021 answer was a helper column with IF, hidden off-screen. With FILTER, the whole thing collapses:

=TEXTJOIN(", ", TRUE, FILTER(B2:B100, A2:A100=E1))

FILTER returns the product names where column A matches the customer in E1. TEXTJOIN glues them together. No helper column, no manual copy step. If the customer has no rows, FILTER returns #CALC!; wrap it in IFERROR to fall back to a friendly string.

With E1 set to Acme, the formula returns Widget, Gasket, Bolt. Change the customer name and the joined list refreshes. That’s the workflow that used to need a pivot table and manual copying.

Deduping the joined list

FILTER returns every match, including repeats. Wrap it in UNIQUE to collapse duplicates before joining:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B100, A2:A100=E1)))

If you want the reverse of this operation — pulling a joined string back apart into rows — see the notes on the TEXTSPLIT function. TEXTJOIN and TEXTSPLIT are mirror images and are usually used together during data cleanup.

Different delimiters for different rows

The delimiter argument accepts a single string, but you can pass an array of strings the same length as the number of items being joined and TEXTJOIN will rotate through them. That’s how you get a comma-and-newline effect between records without a helper column:

=TEXTJOIN({", ", CHAR(10)}, TRUE, A2:B6)

Between values inside a row: a comma. Between rows: a line break. Turn on wrap text on the destination cell or the newlines render as boxes and you’ll think the formula broke.

When TEXTJOIN returns #VALUE!

TEXTJOIN has two hard ceilings and both surface as the same error. The first is the cell limit: the joined string can’t exceed 32,767 characters, because that’s the maximum length any single Excel cell can hold. Join a column of long comments and you can hit it faster than you’d guess. The second is the argument limit: you can supply at most 252 text arguments. A single range argument covers thousands of cells and does not count as thousands of arguments — 252 is only a problem if you’re passing a giant list of individual references, which almost nobody should be doing.

Confirming the limit before you refactor

You don’t have to guess. Wrap the range in SUMPRODUCT with LEN to see the total character count you’re about to build:

=SUMPRODUCT(LEN(A2:A20)) + (COUNTA(A2:A20)-1)*LEN(", ")

The first term sums the length of every value; the second adds the delimiter overhead. If the number is close to 32,767, refactor before you spend time chasing a phantom bug. Microsoft documents the cap in the official TEXTJOIN function reference.

Version availability and the pre-2019 fallback

TEXTJOIN shipped with Excel 2019 and is present in every Microsoft 365 build. Excel 2016 and earlier don’t have it. If you’re stuck on an older build, the closest substitute is CONCAT — but CONCAT also predates 2019, so on genuine 2013/2016 workbooks you’re back to a chained ampersand formula or a small VBA function.

• ✓ Excel 2019 and later: TEXTJOIN available natively
• ✓ Microsoft 365: TEXTJOIN plus FILTER, UNIQUE, SORT for the modern patterns
• ✓ Excel 2016 and older: no TEXTJOIN — use a helper column with IF plus an ampersand chain, or a short custom VBA function
• ✓ Excel for the web and Excel Mobile: TEXTJOIN works identically

If your team uses a mix of versions and shared workbooks, decide the target build before writing formulas that assume dynamic arrays. A TEXTJOIN + FILTER combo saved from Microsoft 365 opens in Excel 2016 as _xlfn.TEXTJOIN(...) — the formula is preserved but the result cell shows #NAME?. Related reading: the FILTER function with multiple criteria covers the same version-boundary issue for the filter side, and the TEXTBEFORE and TEXTAFTER pattern shares the same 2019/365 availability.

The one-line takeaway

TEXTJOIN with ignore_empty set to TRUE replaces the ampersand chain for almost every real join. The two things to remember are that whitespace-only cells slip past ignore_empty, and that the 32,767-character cell limit is a hard error rather than a truncation. Wrap the range in TRIM when the source is dirty, wrap it in FILTER when you only want part of it, and you’ve covered the patterns that show up in real workbooks.