TEXTJOIN

How TEXTJOIN works

TEXTJOIN walks through every cell you give it, sticks the delimiter between each value, and returns one string. The first argument is the separator (a comma, a semicolon, a line break, anything you want). The second is a TRUE/FALSE switch that controls whether empty cells get skipped. Everything after that is the values to join, given as ranges or individual references.

The skip-blanks switch is the part that makes TEXTJOIN better than the old & operator and CONCATENATE. With ignore_empty=TRUE, empty cells in the middle of a range vanish from the output. No double commas, no trailing separators, no manual cleanup.

Pair TEXTJOIN with IF inside an array to filter the list before joining: rows that don't match get returned as empty strings, and ignore_empty=TRUE strips them out. That gives you a one-formula conditional join with no helper column.

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

Arguments

Join a column of names into one comma-separated string

The classic case. A list of names sitting in column A, and you want them rolled up into a single sentence-friendly string. Pick a delimiter, set ignore_empty to TRUE, point at the range.

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

• •", " -> the delimiter: comma followed by a space, the most natural reading separator
• •TRUE -> skip empty cells, so a gap in the middle doesn't produce a double comma
• •A2:A6 -> the range to join, in order, top to bottom
• •Result -> "Sarah, James, Maria, David, Alex" lands in a single cell
• •Read order -> TEXTJOIN walks the range top-down, left-to-right within rows, exactly the order you see them

ignore_empty: TRUE vs FALSE when the source has gaps

Real-world data has holes. A roster with cells someone forgot to fill, an export with missing rows, a list someone half-cleaned. The second argument decides whether TEXTJOIN treats those gaps as nothing or as empty values that still get a delimiter.

Same source range, two formulas, one tiny argument flip. The difference is what the result actually looks like when you paste it into an email.

=TEXTJOIN(", ", TRUE, A2:A8)   vs   =TEXTJOIN(", ", FALSE, A2:A8)

• •TRUE (column C) -> blanks vanish, output reads cleanly: 'Apple, Banana, Cherry, Date, Elderberry'
• •FALSE (column D) -> blanks still get a delimiter on each side: 'Apple, , Banana, Cherry, , Date, Elderberry'
• •Same range, same delimiter -> the only thing changing is the second argument
• •Default behavior -> neither value is the default, you must supply one or the other or Excel rejects the formula
• •Rule of thumb -> use TRUE 95% of the time, FALSE only when you specifically need a fixed-width style with placeholders

Stack address parts on separate lines with CHAR(10)

TEXTJOIN's delimiter doesn't have to be a comma. CHAR(10) is the line-feed character on every modern Excel, which means you can use it as a delimiter to stack values on separate lines inside one cell.

Pulling street, city, and zip into one mailing-label cell is the textbook use. The formula puts a line break between each piece and the result is a properly formatted three-line address sitting in a single cell.

=TEXTJOIN(CHAR(10), TRUE, A2:A4)

• •CHAR(10) -> the line-feed character, a hard line break inside a cell
• •TRUE -> skip blanks so a missing apartment line doesn't leave a gap
• •A2:A4 -> the three address parts: street, city + state, zip
• •Visual catch -> the line breaks ARE in the cell, but they only render visually when Wrap Text is enabled
• •Output cell setup -> select C2, then ribbon Home tab, click Wrap Text, then drag the row taller so all three lines show

TEXTJOIN + IF: list only the active employees

Drop an IF expression inside TEXTJOIN's text argument and you get a one-formula filtered concatenation. The IF returns the name when the status matches, an empty string when it doesn't. ignore_empty=TRUE strips out the empties, leaving a clean comma-separated list of just the rows that qualify.

On Excel 365 you can type this and press Enter; on Excel 2019 wrap it in an array entry with Ctrl+Shift+Enter. Either way, no helper column.

=TEXTJOIN(", ", TRUE, IF(B2:B11="Active", A2:A11, ""))

• •", " -> delimiter between joined names
• •TRUE -> skip empties (the rows IF returned as blank)
• •IF(B2:B11="Active", A2:A11, "") -> row-by-row: if status is Active return the name, otherwise return empty string
• •B2:B11 -> the criterion column (purple range)
• •A2:A11 -> the value column to pull names from (blue range)
• •Result -> "Sarah Chen, Maria Santos, David Brown, Alex Kim, Jenny Liu, Mark Reilly", the six rows where status was Active

Build a paste-ready email distribution string

Outlook, Gmail, and almost every mail client accept a list of recipients separated by semicolons. If your team's emails live in a column, TEXTJOIN with '; ' as the delimiter turns that column into one string you can paste straight into the To: field.

=TEXTJOIN("; ", TRUE, A2:A11)

• •"; " -> semicolon-space, the format Outlook and most mail clients expect between recipients
• •TRUE -> skip blanks so a missing email doesn't produce an invalid double-semicolon
• •A2:A11 -> the email column
• •Workflow -> click the result cell, copy, paste into Outlook's To: field, every address resolves
• •Comma version -> replace "; " with ", " for Gmail and most webmail clients

Where people go wrong

1. 01
   Forgetting the second argument or setting it to FALSE

   If the source range has gaps and ignore_empty is FALSE (or omitted in a way that defaults to FALSE in older clients), the output looks like 'Sarah,,Alex' with double commas wherever a cell was blank. Looks broken to anyone reading it.

   Fix: Default to TRUE for ignore_empty unless you have a specific reason to keep blanks. Type it explicitly every time: =TEXTJOIN(", ", TRUE, A2:A20).
2. 02
   Joining numbers without formatting them first

   TEXTJOIN sees the underlying number, not the cell's display format. A cell showing '$1,234.50' joins as '1234.5' because that's the raw value Excel stores. Currency, percent, and thousands-separator formatting all evaporate.

   Fix: Wrap each numeric reference in TEXT() with the format you want preserved: =TEXTJOIN(", ", TRUE, TEXT(B2:B20, "$#,##0.00")). The TEXT call locks the display format into the joined string.
3. 03
   CHAR(10) line breaks invisible because Wrap Text is off

   You write =TEXTJOIN(CHAR(10), TRUE, A2:A4), expect three stacked lines, and the cell shows everything on one line with weird square boxes. The line breaks are in the cell, they're just not being rendered.

   Fix: Select the result cell, ribbon Home tab, click Wrap Text. Then drag the row height taller until all the lines show. Wrap Text is per-cell, so applying it once doesn't carry to other cells you fill the formula into.
4. 04
   Hitting the 32,767-character cap on huge joins

   TEXTJOIN's result is capped at 32,767 characters, the same limit as any Excel cell. Joining a column of 5,000 long descriptions returns #VALUE! when the string would exceed the cap. The error gives no hint that length was the cause.

   Fix: Split the join into chunks (=TEXTJOIN(...) for the first 1,000, another for the next, then concatenate the chunks), or filter the source range first to cut the row count down. If you genuinely need that much text in one place, Excel is the wrong tool, use Power Query or a database.

Notes

• TEXTJOIN was added in Excel 2019. It's available on Excel 2019, Excel 2021, Excel 365, and Excel Online, on Windows, Mac, iOS, Android, and the web.
• CHAR(10) is the line-feed character on every modern Excel. CHAR(13) is carriage-return; you almost never need it on its own. Combine them as CHAR(13)&CHAR(10) only if you're exporting to a system that demands Windows-style CRLF inside the cell.
• The second argument (ignore_empty) is required, no default. Excel rejects =TEXTJOIN(", ", A2:A6) outright.
• text1, text2, ... can be ranges, single cells, or literal strings. Mix and match: =TEXTJOIN(", ", TRUE, "Team:", A2:A6) prefixes the joined list with the word Team:.
• Combine TEXTJOIN with IF (TEXTJOIN(", ", TRUE, IF(condition, value, ""))) for conditional concatenation in one cell, no helper column.
• Pair TEXTJOIN with TRIM if the source data has stray leading or trailing spaces: =TEXTJOIN(", ", TRUE, TRIM(A2:A20)). Cleans up imported data before it lands in the joined string.
• Older Excel versions (2016 and earlier) don't have TEXTJOIN. Falling back means using & (a&", "&b&", "&c) or CONCATENATE, neither of which can skip blanks, so empty cells leave double delimiters in the output.

What to learn next

Now prove it