The Excel TEXTJOIN function, explained interactively.
TEXTJOIN combines a list of text values into a single string, placing a delimiter between every pair. The successor to CONCATENATE — it accepts ranges and offers an ignore_empty toggle so blanks don’t create dangling separators.
How to use TEXTJOIN
- Type
=TEXTJOIN(and supply the delimiter — the string placed between each pair of pieces. A comma + space is typical, but any string works, including line breaks viaCHAR(10). - Add a comma and the ignore_empty flag —
TRUEskips blank cells;FALSEpreserves them, producing back-to-back delimiters. - Add the text1 argument — usually a range like
A2:A100. You can also pass individual cells or literal strings, up to 252 arguments total. - Close the parenthesis. TEXTJOIN returns a single string up to 32,767 characters long; longer outputs return
#VALUE!.
Try the demo — change ignore_empty to FALSE and watch the output grow extra commas where the blank rows sit.
""), a comma, a line break (CHAR(10)), or anything else.A2:A8), individual references, or a mix. TEXTJOIN flattens it all into one stream.| A | B | |
|---|---|---|
| 1 | Tag | Joined |
| 2 | Food | |
| 3 | (blank) | |
| 4 | Travel | |
| 5 | Office | |
| 6 | (blank) | |
| 7 | Supplies | |
| 8 | Utilities |
", " between pieces. ignore_empty is TRUE — skipped 2 empty cells so the output has no dangling delimiters.TEXTJOIN syntax and arguments
One delimiter, one ignore_empty flag, then up to 252 text arguments. See Microsoft’s official TEXTJOIN reference for the canonical specification.
"" (empty string, no separator) is valid. Can be a literal, a cell reference, or a function call like CHAR(10) for a line break.TRUE skips empty cells so no dangling delimiters appear in the output. FALSE keeps them, producing back-to-back delimiters (a,,b). Defaults to TRUE when omitted.A2:A100), a single cell reference, a literal string ("extra"), or any formula that returns text. TEXTJOIN flattens every piece into one ordered stream before joining.TEXTJOIN examples
Four patterns that cover the overwhelming majority of real-world TEXTJOIN use.
Example 1: TEXTJOIN for a comma-separated list
The canonical case — collapse a column of tags into one readable string.
Comma + space between each piece, empty cells skipped. Identical to what most people would paste into the body of an email or the caption of a chart.
Example 2: TEXTJOIN with a line break
Stack values vertically inside a single cell — handy for exporting a bulleted list or a printable summary.
Turn on Wrap Text in the cell or the line breaks stay hidden. On Windows, CHAR(13)&CHAR(10) (CR+LF) is sometimes required for external consumers.
Example 3: TEXTJOIN combining a range with literals
Build a sentence from data plus fixed phrasing.
Literal strings and ranges mix freely. Result looks like Selected: Food Travel Office (end). Use ", " as the delimiter for a more natural comma-separated output.
Example 4: TEXTJOIN to build a WHERE clause
SQL-like usage: build a clause from a list of IDs that lives in a column.
Concatenate TEXTJOIN’s output into the larger string with &. Pair with wildcards or quote each id by writing "'"&A2&"'" in a helper column before joining.
Common TEXTJOIN errors and fixes
Four failure modes, each with what to check and how to recover.
TEXTJOIN returns #VALUE!
Cause: the joined string exceeds 32,767 characters (Excel’s cell limit) or a text argument evaluates to an error.
Split the source into halves and join with another TEXTJOIN or &. To filter out errors, wrap each argument in IFERROR(..., "") before passing it.
TEXTJOIN has extra commas in the output
Cause: ignore_empty is FALSE, so blank cells leave back-to-back delimiters (a,,b).
Switch ignore_empty to TRUE. If you need to preserve the structural position, leave FALSE and clean up downstream with SUBSTITUTE.
TEXTJOIN line breaks not showing
Cause: the delimiter is CHAR(10), but the cell doesn’t have Wrap Text enabled.
Home → Alignment → Wrap Text. In Google Sheets, Format → Wrapping → Wrap. Without wrapping, the newline is present but not rendered.
TEXTJOIN returns #NAME? in old Excel
Cause: TEXTJOIN was introduced in Excel 2019 / Office 365. Excel 2016 and earlier don’t recognise it.
Substitute a helper column that appends the delimiter per row (=A2&", "), then concatenate with CONCATENATE or &. Less elegant, but works everywhere.
TEXTJOIN vs CONCAT, CONCATENATE & the & operator
Four ways to combine text. Pick based on whether you need a delimiter, whether ranges are allowed, and which Excel version you ship to.
| Function | Delimiter | Ranges | Available in |
|---|---|---|---|
| TEXTJOIN | Yes — one shared delimiter | Yes, including full columns | Excel 2019+, Sheets |
| CONCAT | No | Yes | Excel 2019+, Sheets |
| CONCATENATE | No | No — list each cell | Legacy, all versions |
| & operator | Manual between each pair | No | All versions |
Rule of thumb: if you need a delimiter and the target is Excel 2019 or newer, TEXTJOIN wins every time. CONCAT is the delimiter-free equivalent — useful when piecing together fragments whose separators are already embedded. Reach forCONCATENATE or & only when shipping to Excel 2016 or earlier.
TEXTJOIN frequently asked questions
6.01What’s the difference between TEXTJOIN and CONCATENATE?▸
CONCATENATE pastes values end-to-end with no delimiter and does not accept ranges — you list each cell individually. TEXTJOIN takes a delimiter argument and accepts ranges directly, so joining A1:A100 with commas is one argument instead of a hundred.
6.02How do I add a line break between joined values?▸
Use CHAR(10) as the delimiter: =TEXTJOIN(CHAR(10), TRUE, A2:A10). On Windows you may need CHAR(13)&CHAR(10). Turn on Wrap Text in the cell so the line breaks are actually rendered.
6.03Can TEXTJOIN combine a range with individual cells?▸
Yes. =TEXTJOIN(", ", TRUE, A2:A10, B1, "extra") is valid — ranges and single references are both accepted as text arguments. Up to 252 text arguments in total.
6.04Why does TEXTJOIN return #VALUE!?▸
The most common cause is exceeding the 32,767-character result limit. Break the source range into smaller chunks and concatenate the TEXTJOIN calls. The other cause is an invalid text argument (e.g. an array that resolves to error).
6.05Does TEXTJOIN work in older Excel versions?▸
TEXTJOIN was introduced in Excel 2019 and Excel 365 (and has always existed in Google Sheets). In Excel 2016 or earlier, fall back to CONCATENATE or & with a helper column that inserts the delimiter.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.