Fixing Excel TEXTJOIN That Returns Empty String When Range Has Blanks

You write a TEXTJOIN formula, hit Enter, and get back an empty string or a delimiter-only result like ,,. The data is right there in the range — you can see it. But Excel returns nothing useful.

This is almost always caused by one of two things: the ignore_empty argument is set wrong, or the range you're joining contains values that look empty but aren't. Both are fixable in under five minutes once you know where to look.

What you'll learn

• What the ignore_empty argument actually controls and why its default behavior trips people up
• Why TEXTJOIN can return a blank even when ignore_empty is TRUE
• How to use IF inside TEXTJOIN to join only cells that meet a condition
• How to combine TEXTJOIN with FILTER for cleaner dynamic ranges
• Edge cases involving zero-length strings, formulas returning "", and number formats

What's actually going wrong

TEXTJOIN has the following signature:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

The ignore_empty argument is a boolean. When it's TRUE, TEXTJOIN skips cells that are genuinely empty. When it's FALSE, those empty cells still contribute to the output — which means you get consecutive delimiters for every blank in the range.

The silent failure mode — where TEXTJOIN returns a completely empty string — usually means every cell in the evaluated range resolved to an empty value. That can happen because the cells are blank, because they contain formulas returning "", or because the range reference itself is wrong.

Understanding the ignore_empty argument

Think of ignore_empty as a filter switch. With it set to TRUE, Excel evaluates each cell in the range and simply discards the ones that are empty before joining. With it set to FALSE, empty cells are treated as participants — they produce nothing between their delimiters, but the delimiters are still emitted.

This distinction matters most when your range is sparse. A column of 10 cells where only 3 have values will produce very different results depending on this flag:

ignore_empty value	Result (delimiter is comma)
FALSE	Apple,,,,Banana,,,Orange,,
TRUE	Apple,Banana,Orange

Most of the time, TRUE is what you want. The confusion arises because some users expect FALSE to be the "inclusive" option and TRUE to be the restrictive one — the naming is counterintuitive if you read it as "ignore empty = skip things."

The classic mistake: passing FALSE as ignore_empty

Here's a formula that produces a long chain of commas instead of a clean list:

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

If A2:A10 has values only in A2, A5, and A9, you get Apple,,,,Banana,,,Orange,. If the range is completely empty, you get an empty string. Neither output is wrong from Excel's perspective — it's doing exactly what you told it to do.

The fix here is straightforward: change FALSE to TRUE.

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

That gives you Apple,Banana,Orange with no extra delimiters. If you're not sure whether your formula uses TRUE or FALSE, click the cell, go to the formula bar, and check the second argument directly.

Fixing it with TRUE — and when that's not enough

Setting ignore_empty to TRUE fixes the obvious blank-cell problem, but it does not fix cells that appear blank without actually being empty. The most common culprit is a formula that returns "".

Suppose column B contains formulas like this:

=IF(A2="complete", C2, "")

When A2 is not "complete", B2 holds a zero-length string — "". This is not the same as an empty cell. TEXTJOIN with ignore_empty = TRUE will still skip it in most cases, but the behavior can vary if those results feed into an array context. Verify by pressing F2 on a formula cell and checking whether it evaluates to "" or to a true empty.

If zero-length strings from formulas are leaking through, the cleanest solution is to wrap the TEXTJOIN range in an IF that explicitly filters them out, which is covered in the next section.

When TEXTJOIN still returns empty after setting TRUE

If you've set ignore_empty to TRUE and you're still getting an empty string, the range itself is the problem. Walk through these checks in order:

1. Verify the range address. Click the cell with TEXTJOIN, press F2, then use Ctrl+Shift+End to confirm the range highlights the cells you expect. A typo like A2:A1 (reversed) returns nothing.
2. Check for number-formatted-as-text issues. If cells contain numbers stored as text and your delimiter logic depends on numeric comparisons elsewhere, those values may resolve differently than expected. Use VALUE() or TEXT() to normalize them before joining.
3. Look for hidden characters. Cells that appear blank but contain a space or a non-breaking space (CHAR(160)) are not empty. TEXTJOIN will include them. Use TRIM(CLEAN()) to strip those before passing to TEXTJOIN.
4. Check whether the sheet is filtered. TEXTJOIN operates on all cells in a range regardless of filter state — it does not skip hidden rows. If you need to join only visible rows, you need a different approach (see the FILTER section below).

For issues where hidden whitespace is the culprit, a preprocessing column helps. Add a helper column with =TRIM(CLEAN(A2)) and reference that column in your TEXTJOIN instead of the raw source.

Using IF inside TEXTJOIN to filter conditionally

Sometimes you don't want to join every non-empty cell — you want to join cells that meet a condition. You can embed an IF array directly inside TEXTJOIN to do this without a helper column.

Say you have a list of tasks in column A and their status in column B. You want a comma-separated list of only the "Done" tasks:

=TEXTJOIN(",", TRUE, IF(B2:B10="Done", A2:A10, ""))

In older Excel versions (pre-dynamic array), you'd need to enter this as an array formula with Ctrl+Shift+Enter. In Excel 365 and Excel 2019+, it works as a regular formula because dynamic arrays handle the implicit intersection automatically.

The IF returns an array where matching cells show the task name and non-matching cells show "". TEXTJOIN with ignore_empty = TRUE then skips all the "" entries and joins only the matches. This is a reliable pattern for conditional text joining that avoids helper columns entirely.

For related conditional-range logic, the same mental model applies when you're debugging COUNTIF with wildcard criteria — understanding how Excel evaluates arrays inside aggregate functions makes both problems click.

Combining TEXTJOIN with FILTER for dynamic ranges

If you're on Excel 365 or Excel 2021, the FILTER function gives you a cleaner alternative to the IF-inside-TEXTJOIN pattern. FILTER returns a subset of a range based on a condition, and TEXTJOIN can consume that subset directly.

=TEXTJOIN(",", TRUE, FILTER(A2:A10, B2:B10="Done", ""))

The third argument to FILTER ("") tells it what to return when nothing matches, preventing the #CALC! error that FILTER throws by default when the condition matches zero rows. Wrapping that in TEXTJOIN with ignore_empty = TRUE means even if FILTER returns a single empty string, TEXTJOIN outputs nothing rather than crashing.

FILTER also respects the actual data in the range rather than a static address, so if your source list grows and you've referenced a full column (A:A), the joined result updates automatically. Just be careful with full-column references in TEXTJOIN — joining thousands of rows is slower than joining a bounded range.

One thing FILTER does handle that raw IF arrays don't: multi-column output. You can FILTER multiple columns and then join them with a nested TEXTJOIN or use BYROW if you need per-row concatenation. That's a more advanced pattern, but useful if you're building report strings from several fields.

Common pitfalls and edge cases

TEXTJOIN character limit

The result of TEXTJOIN is capped at 32,767 characters — the same limit as any Excel cell. If your joined string exceeds this, Excel returns a #VALUE! error. This is easy to hit when joining large ranges. If you're approaching this limit, consider splitting the join across multiple cells or summarizing the data differently.

Joining numbers loses formatting

TEXTJOIN converts numbers to their default string representation. A value formatted as currency ($1,200.00) will join as 1200. Wrap numbers in TEXT() with an explicit format string before passing them to TEXTJOIN:

=TEXTJOIN(",", TRUE, TEXT(A2:A10, "$#,##0.00"))

Dates behave the same way — they serialize to their underlying numeric value unless you TEXT() them first. This is a common source of confusion when building report strings that mix dates and labels.

Misaligned ranges inside IF arrays

When you write IF(B2:B10="Done", A2:A10, ""), both ranges must be the same size. If they're different lengths, Excel returns a #VALUE! error. Double-check that your condition range and your value range have identical row counts. This is the same class of problem as misaligned ranges in SUMIFS — Excel evaluates them in parallel and breaks when the dimensions don't match.

IFERROR masking real problems

It's tempting to wrap TEXTJOIN in IFERROR to suppress errors, but doing so can hide the actual cause. If TEXTJOIN returns #VALUE! because of misaligned ranges or an exceeded character limit, wrapping it in IFERROR will silently return a blank instead of pointing you to the real issue. Diagnose first, then add IFERROR if you genuinely want a fallback. The same principle applies broadly — as covered in our guide to IFERROR hiding real errors.

TEXTJOIN on a single cell returning empty

If you're joining a single cell and it returns empty, the issue is almost certainly that the cell itself contains "" from a formula rather than a genuine value. Press F2 on the source cell to see what it evaluates to. If it shows an empty formula bar but the cell was set by a formula, that formula is returning a zero-length string. Trace that upstream formula to fix the source, rather than patching TEXTJOIN to work around it.

Wrapping up

TEXTJOIN's empty-string failure usually traces back to one of three root causes: ignore_empty is FALSE when it should be TRUE, cells in the range contain formula-generated "" rather than genuine values, or the range reference is wrong. Here's what to do next:

1. Set ignore_empty to TRUE in any TEXTJOIN formula that deals with a sparse range. That's the single most common fix.
2. Audit your source cells with F2 to confirm whether "blank-looking" cells are genuinely empty or contain zero-length strings from upstream formulas.
3. Use IF(condition, range, "") inside TEXTJOIN when you need conditional joining without a helper column.
4. Switch to FILTER + TEXTJOIN if you're on Excel 365 and want cleaner syntax with automatic range expansion.
5. Wrap numbers and dates in TEXT() before joining if you need formatted output rather than raw numeric values.

Once you understand what ignore_empty actually controls and what "empty" means in an array context, TEXTJOIN becomes a reliable tool rather than a frustrating black box. For more Excel formula debugging patterns, the same diagnostic mindset applies when VLOOKUP returns #N/A due to trailing spaces — invisible characters are a recurring theme across Excel's formula engine.