For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=FUNCTIONS

TEXTJOIN()

Text

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

Excel worksheet with a Name column containing a blank cell and a TEXTJOIN formula producing a comma-separated list, with a callout about the ignore_empty argument.
=TEXTJOIN(", ", TRUE, A2:A10)
→Alex, Priya, Sam, Jordan

A comma-separated list from a column, skipping any blanks.

=TEXTJOIN(", ", TRUE, IF(B2:B100="North", A2:A100, ""))
→Acme, Borden, Craft

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)
→Line 1 ⏎ Line 2 ⏎ …

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_empty is the reason to choose TEXTJOIN over CONCAT. Set TRUE and 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 TEXT to preserve currency or date formats.
  • Capped at 32,767 characters, and unavailable before Excel 2019.

Common errors

What goes wrong with TEXTJOIN, and why
You seeBecauseFix
#VALUE!The result exceeds the cell character limit.Reduce the range, or split the output.
Double separators appearignore_empty is FALSE.Set the second argument to TRUE.
Tips / Tricks / Hacks

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 tricks and formula patterns
TEXTJOIN patternTEXTJOIN formulaBest useWatch 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.
Common TEXTJOIN mistakes and how to fix them
TEXTJOIN mistakeWhat happens in TEXTJOINFix
Exceeding Excel's 32,767-character cell limitJoining 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

  1. 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.

Related functions