Function guides ยท Text
TEXTJOIN function
Joins text from a range with a delimiter between each item, optionally skipping blanks.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
| Argument | What it is |
|---|---|
delimiter | The separator placed between items, in quotes, such as ", ". |
ignore_empty | TRUE skips empty cells. FALSE keeps them, leaving doubled separators. |
text1, ... | Text, cells or ranges to join. |
What TEXTJOIN does
TEXTJOIN joins text from a range or a list of cells and puts a separator between the pieces. It can also skip the empty ones, which is what makes it more useful than CONCAT.
The first argument is the separator, in quotes. The second says whether to ignore empty cells. After that come the cells or ranges to join.
It is built for lists: skills in a cell, names in a sentence, address lines that may or may not exist. It arrived in Excel 2019, so a test built on 2016 cannot use it.
Because TEXTJOIN accepts a range, it replaces long chains of ampersands. A mailing address built from five fields becomes one formula that ignores whichever fields are empty. A list of tags kept in several columns becomes one cell for export.
For a list that is built from a filtered subset, combine it with FILTER in Microsoft 365: =TEXTJOIN(", ",TRUE,FILTER(B2:B20,A2:A20="West")). In Excel 2019 the same result needs an IF inside TEXTJOIN and Ctrl+Shift+Enter.
TEXTJOIN examples
1. A comma-separated list from a range
| A | B | |
|---|---|---|
| 1 | Skill | Excel, SQL, Reporting |
| 2 | Excel | |
| 3 | SQL | |
| 4 | ||
| 5 | Reporting |
One formula joins the whole range with a comma and a space between items. TRUE tells it to leave out the empty cell, so no doubled separator appears.
2. Address line that skips a missing unit
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Street | Unit | City | |
| 2 | 410 Ala Moana | Honolulu | 410 Ala Moana, Honolulu |
Many addresses have no unit number. With ignore_empty set to TRUE, the blank unit is skipped and the line reads cleanly.
3. What FALSE does
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Street | Unit | City | |
| 2 | 410 Ala Moana | Honolulu | 410 Ala Moana,,Honolulu |
With ignore_empty set to FALSE, the empty cell still gets its separator, giving two commas in a row. Use FALSE only when the position of each item matters, such as building a CSV line.
Common TEXTJOIN mistakes
- Forgetting the second argument. ignore_empty is required. Excel will not accept
=TEXTJOIN(", ",A2:A5): it says you have entered too few arguments. Type TRUE or FALSE as the second argument. - Using it in Excel 2016. TEXTJOIN is in Excel 2019 and Microsoft 365. Older versions show #NAME?.
- Delimiter without quotes. The separator is text, so wrap it in double quotes.
- Dates and numbers in the range. A date joins as a serial number. Format it with TEXT first, or add a helper column.
- Ranges that include totals or headers. They are joined too. Select only the cells you want.
TEXTJOIN on Excel job tests
To "list the skills for each candidate in one cell" or "build a mailing address, leaving out the empty unit line", use TEXTJOIN. Set the delimiter to match the example in the task character for character, including the space after the comma, and use TRUE to skip blanks.
A conditional list puts an IF inside: TEXTJOIN(", ",TRUE,IF(A2:A20="West",B2:B20,"")). In Excel 2019 that needs Ctrl+Shift+Enter. In Microsoft 365, FILTER is cleaner.
TEXTJOIN does not exist in Excel 2016.
Related functions
TEXTJOIN questions
- What is the difference between CONCAT and TEXTJOIN?
- TEXTJOIN puts a delimiter between items and can skip empty cells. CONCAT joins with nothing in between.
- How do I skip blank cells?
- Set the second argument to TRUE.
- Can TEXTJOIN join only some items?
- Yes, with an IF inside it. In Excel 2019 confirm with Ctrl+Shift+Enter.
- Is there a limit on length?
- The result is limited to 32,767 characters, the maximum for a cell.
Practice TEXTJOIN
The lesson Joining text: &, CONCAT and TEXTJOIN (module 9 of Everyday Excel) has checked TEXTJOIN exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample