TEXTJOIN joins text from many cells using a separator you choose, and it can skip empty cells.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
- delimiter: the text placed between items, such as
", ".
- ignore_empty:
TRUE skips blank cells, FALSE keeps them.
- text1: a cell, a range or text.
Join a range with commas
=TEXTJOIN(", ", TRUE, A2:A10)
If A2:A10 has Apple, Mango and Banana with blanks in between, you get Apple, Mango, Banana with no extra commas.
Why not use & or CONCATENATE?
With & you have to add every cell and separator by hand. CONCATENATE does not accept ranges. TEXTJOIN does it in one short formula.
Join only items that meet a condition
Combine it with FILTER:
=TEXTJOIN(", ", TRUE, FILTER(A2:A100, B2:B100="Pending"))
This lists every item whose status is Pending, in one cell.
Line breaks
Use CHAR(10) as the delimiter and turn on Wrap Text to put each item on its own line.
TEXTJOIN is available in Excel 2019 and Microsoft 365.
Worked example
Cities are in A2:A6, with one blank. =TEXTJOIN(", ",TRUE,A2:A6) returns "Pune, Mumbai, Delhi" and skips the blank. Use FALSE as the second argument to keep blanks.
Common mistakes
- Using it in Excel 2016 or older. It needs 2019 or Microsoft 365.
- Forgetting the delimiter, so text runs together.
- Joining a huge range and hitting the 32,767 character limit.
Frequently asked questions
TEXTJOIN or CONCAT?
TEXTJOIN adds a delimiter and can skip blanks. CONCAT only joins.
Can TEXTJOIN use a condition?
Yes: =TEXTJOIN(", ",TRUE,IF(B2:B20="Paid",A2:A20,"")).
Can I join with a line break?
Use CHAR(10) as the delimiter and turn on Wrap Text.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: TEXTJOIN, CONCAT, FILTER.
Start Excel practice → Case studies
