TEXTJOIN()
Joins text with a delimiter between each item, and can skip blank cells.
TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)TEXTJOIN combines CONCAT's ability to join whole ranges with a delimiter placed between every item — a comma-separated list from a column is a single formula: TEXTJOIN(", ", TRUE, A2:A10).
Setting ignore_empty to TRUE skips blank cells instead of leaving an extra delimiter where they would have been.
TEXTJOIN is the right function whenever the output is a readable list rather than a single run of characters, because the delimiter and the ignore-empty flag are exactly the two things CONCAT lacks. The second argument deserves attention: set to TRUE it drops empty cells, which is usually what you want; set to FALSE it preserves them, which produces the double separators that reveal where data is missing. The result is capped at 32,767 characters, so joining a very long column returns #VALUE! rather than a truncated string.
Arguments
- delimiter
- The text to insert between each joined item.
- ignore_empty
- TRUE to skip blank cells, FALSE to include them as empty items.
- text1
- The first text value, cell, or range to join.
- text2, ...optional
- Additional text values, cells, or ranges to join.
Examples

=TEXTJOIN(", ", TRUE, A2:A10)A comma-separated list from a column, skipping any blanks.
=TEXTJOIN(", ", TRUE, IF(B2:B100="North", A2:A100, ""))Joins only the rows meeting a condition — a one-cell list of everything in a category. Needs Ctrl+Shift+Enter before Microsoft 365.
=TEXTJOIN(CHAR(10), TRUE, A2:A6)CHAR(10) joins with line breaks. The cell must have Wrap Text turned on or it all shows on one line.
Notes and gotchas
- The second argument
ignore_emptyis the reason to choose TEXTJOIN over CONCAT. SetTRUEand blank cells are skipped — otherwise you get double separators where values are missing. - The delimiter can be multiple characters, and
CHAR(10)gives a line break inside a cell (turn on Wrap Text to see it). - It accepts ranges and arrays, so
TEXTJOIN(", ", TRUE, FILTER(names, dept="Sales"))builds a conditional list in one formula. - Numbers are converted to text with their general formatting, not their displayed formatting. Wrap in
TEXTto preserve currency or date formats. - Capped at 32,767 characters, and unavailable before Excel 2019.
Common errors
| You see | Because | Fix |
|---|---|---|
| #VALUE! | The result exceeds the cell character limit. | Reduce the range, or split the output. |
| Double separators appear | ignore_empty is FALSE. | Set the second argument to TRUE. |
TEXTJOIN tricks, tips, and workbook hacks
TEXTJOIN solves the two things CONCAT and & always made painful: it inserts your chosen delimiter only between items (never leaving a dangling comma at the end), and when ignore_empty is set to TRUE, it skips blank cells automatically so missing middle names or empty address lines never produce double commas.
Paired with IF or FILTER in Excel 365, TEXTJOIN becomes a conditional string aggregator—the text equivalent of SUMIFS—letting you roll up all product names or invoice numbers belonging to one customer into a single comma-separated cell.
Quick TEXTJOIN checks before trusting the result
- Set the 2nd argument (ignore_empty) to TRUE in almost every real-world scenario so blank cells don't generate ", ," artifacts.
- Use CHAR(10) as the delimiter (with Wrap Text turned on) to stack multiple items on separate lines inside one cell.
- Combine with UNIQUE and FILTER to list every distinct product a customer ordered in one cell.
| TEXTJOIN pattern | TEXTJOIN formula | Best use | Watch out |
|---|---|---|---|
| Conditional text rollup (the 'TEXTJOINIFS' pattern) | =TEXTJOIN(", ",TRUE,IF(A2:A500=F2,B2:B500,"")) | Listing every invoice number or project member belonging to Client F2 in a single summary cell. | IF returns "" for non-matching rows, and ignore_empty=TRUE skips all of those empty strings! |
| Clean mailing address with optional Suite / Line 2 | =TEXTJOIN(", ",TRUE,B2:F2) | Joining Street, Apt/Suite, City, State, and ZIP where Apt/Suite is blank on 70% of rows. | Zero double-commas when Apt/Suite is empty. |
| Multi-line cell summary using CHAR(10) | =TEXTJOIN(CHAR(10),TRUE,FILTER(B2:B200,A2:A200=E2,"None")) | Building printable calendar cells or project milestone cards with one item per line. | Enable Home → Wrap Text on the formula column so CHAR(10) renders as a line break. |
| TEXTJOIN mistake | What happens in TEXTJOIN | Fix |
|---|---|---|
| Exceeding Excel's 32,767-character cell limit | Joining thousands of rows into one cell throws #VALUE! (or #CALC!). | Filter the array first or wrap inUnique() so you only join relevant items. |
| Setting ignore_empty to FALSE when combining IF() | =TEXTJOIN(", ", FALSE, IF(A2:A20=F2, B2:B20, "")) outputs ", , , Widget, , , Gadget". | Always set the 2nd argument to TRUE when feeding an IF() array into TEXTJOIN. |
TEXTJOIN practice drills
- Create an order log where 3 customers each buy 2–4 products. Use =TEXTJOIN(", ", TRUE, IF($A$2:$A$20=E2, $B$2:$B$20, "")) to list each customer's products in one cell.



