TEXTJOIN Function in Excel
TEXTJOIN is the modern way to combine text values with a delimiter between them — the replacement for daisy-chaining & operators or fighting with CONCATENATE. It takes ranges directly, skips empty cells automatically, and handles thousands of values in one call.
What it does: Joins multiple text values (or ranges of values) into a single string, separated by delimiter. Optionally skips empty cells so you don't get double delimiters or trailing separators.
What TEXTJOIN does
Before TEXTJOIN, combining a range of text values into one delimited string was painful. You'd chain =A1 & ", " & A2 & ", " & A3 & ... — brittle, non-scalable, and if any cell was empty you'd get "Anna, , Bob" with a stray comma.
TEXTJOIN solves both problems: pass a range directly, set the second argument to TRUE to skip blanks, and get a clean output. It's the function you reach for whenever you need a delimited list, a formatted address, a sentence built from cell values, or a comma-separated string for another tool.
TEXTJOIN + FILTER. Combine a delimiter, blank-skipping, and a dynamic-array filter to get "all matching items as a comma-separated list": =TEXTJOIN(", ", TRUE, FILTER(Names, Region="East")). This is the single most powerful pattern TEXTJOIN unlocks.
Syntax breakdown
delimiter Required
The string that goes between each text value. Common choices: ", " (comma-space), " " (single space), CHAR(10) (line break inside a cell), " | " (pipe). Can be any string, including multi-character.
ignore_empty Required
TRUE skips empty cells (no delimiter added for them); FALSE includes them as empty strings (produces double delimiters). Almost always use TRUE — the exception is fixed-position formats like CSV where empty fields matter.
text1, text2, ... Required
The text values or ranges to join. Can be individual cells, ranges (like A2:A100), or literal strings. Up to 252 text arguments; each range can contain many cells.
5 real-world examples
Example 1: Basic comma-separated list
Column A has names. Combine them all into one string:
=TEXTJOIN(", ", TRUE, A2:A10)Result: Anna, Bob, Carla, David, Emma, Fred, Gina, Henry, Isabel. Empty cells in the range are skipped so there are no double commas.
Example 2: Build a full address from parts
Cells B2 to B6 have street, apt, city, state, zip. Some (like apt) may be empty:
=TEXTJOIN(", ", TRUE, B2:B6)Result: 123 Main St, San Francisco, CA, 94103 — the empty apt field skipped without leaving a hanging comma.
Example 3: Multi-line list with CHAR(10)
Combine tasks into a bullet list inside a single cell (with wrap enabled):
=TEXTJOIN(CHAR(10), TRUE, "• "&A2:A10)Result: A cell showing each task on its own line, prefixed with a bullet. Turn on "Wrap Text" for the cell to see the line breaks.
Example 4: Conditional join with FILTER
Comma-separated list of all customers in the East region:
=TEXTJOIN(", ", TRUE, FILTER(Customers[Name], Customers[Region]="East"))Result: Alice Chen, Bob Smith, Carla Ng, David O'Brien. Only names where region is East, joined by commas. Requires Excel 365 or 2021+ for FILTER.
Example 5: Sentence from multiple cells
Build a sentence by mixing literals and cell values:
=TEXTJOIN(" ", TRUE, "Order", A2, "for", B2, "shipped on", TEXT(C2, "mmm d"))Result: Order 8347 for Alice Chen shipped on Mar 15. The space delimiter joins each piece; TEXT formats the date properly.
Common errors and how to fix them
TEXTJOIN doesn't exist in your Excel version — likely Excel 2016 or earlier. Fix: upgrade to Excel 2019 / 365, or fall back to CONCATENATE combined with IF (verbose but works).
Usually caused by a range or value that TEXTJOIN can't convert to text, or by exceeding the 32,767-character output limit. Fix: check that all inputs are text-compatible; for huge outputs, split into multiple TEXTJOINs.
Second argument is FALSE — change it to TRUE to skip empties. Also check for cells containing spaces or invisible characters that look empty but aren't; use TRIM in a helper column.
TEXTJOIN converts numbers to their raw form (dates become serial numbers, currencies lose formatting). Fix: wrap each value in TEXT() with the desired format string.
TEXTJOIN vs alternatives
| Function | Best for | Watch out for |
|---|---|---|
| TEXTJOIN | Delimited lists from ranges, ignore empties | Excel 2019+ only |
| CONCAT | Combining many cells without delimiter | Excel 2019+; no way to skip empties |
| CONCATENATE | Legacy joining, individual cells only | No range support in older versions |
| & operator | Combining a few cells inline | Painful for many cells or ranges |
| TEXTSPLIT (reverse) | Splitting a joined string back apart | Excel 365 only |
Version compatibility
Download the practice workbook
Every example above, plus a template for building comma-separated lists from filtered ranges.
Related functions
Frequently asked questions
Why doesn't TEXTJOIN work in my Excel 2016?
Because TEXTJOIN was added in Excel 2019. Older versions don't have it. Workarounds: upgrade Excel, use nested CONCATENATE, or use the & operator with IF checks to skip blanks. None of these scale well to large ranges — TEXTJOIN is a genuine step change.
How do I add a line break inside a TEXTJOIN result?
Use CHAR(10) as the delimiter: =TEXTJOIN(CHAR(10), TRUE, A2:A10). Then format the destination cell with "Wrap Text" on so the line breaks show up. Without Wrap Text, they display as small squares or hidden characters.
Can TEXTJOIN combine values from multiple sheets?
Yes — pass each sheet's range as a separate argument: =TEXTJOIN(", ", TRUE, Sheet1!A2:A100, Sheet2!A2:A100). All values are joined into one string.
How do I make TEXTJOIN put "and" before the last item?
No built-in support, but you can construct it: =SUBSTITUTE(TEXTJOIN(", ", TRUE, range), ", " & INDEX(range, COUNTA(range)), " and " & INDEX(range, COUNTA(range))). Or, more readably, handle it as two ranges — one for everything but last, one for the last item.
Why do dates come out as numbers?
Because TEXTJOIN uses the raw underlying value (dates are stored as serial numbers). Wrap dates in TEXT: =TEXTJOIN(", ", TRUE, TEXT(A2:A10, "yyyy-mm-dd")) to format them as text before joining.
What's the maximum length of a TEXTJOIN output?
32,767 characters — Excel's cell content limit. If your join exceeds this, you'll get #VALUE!. Split into multiple cells, or process in batches.
How do I make TEXTJOIN handle unique values only?
Wrap the range in UNIQUE first: =TEXTJOIN(", ", TRUE, UNIQUE(A2:A100)). Removes duplicates before joining. Requires Excel 365 or 2021+ for UNIQUE.
Build lists and joins instantly — with the Sheets & Cells AI Add-in
Describe the delimited output you want — "comma-list of active customer names by region" — and the Add-in writes the TEXTJOIN + FILTER combination. All inside Excel, no formula bar wrestling.
Learn about the Add-in →