TEXTJOIN - Combine fields with a delimiter
Build a display name or a list from several text values, choosing whether missing fields should leave empty positions.
By the end, you can
- Choose a delimiter and empty-cell policy.
- Join a range or several text arguments.
- Keep joining separate from cleanup and deduplication.
Before you start: Understand separate text fields and genuinely empty cells.
In this lesson
A delimiter between included values
TEXTJOIN combines text items using a delimiter such as a space, comma, or hyphen. An HR report can assemble separately supplied given, middle, and family fields into a display name. It can also make a compact recipient list. Joining does not prove the fields are valid, unique, or correctly spaced.
Syntax and arguments
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)The delimiter and empty-cell setting are explicit. A range counts as one text argument even when it has many cells.
| Argument | Purpose | Example |
|---|---|---|
delimiterRequired | Text inserted between included values; empty text is allowed. | " " |
ignore_emptyRequired | TRUE skips empty cells; FALSE keeps their positions and delimiters. | TRUE |
text1Required | The first text item or range to combine. | A2:C2 |
text2, …Optional | Additional items or ranges, up to 252 text arguments total. | E2 |
Names from separate source fields
Anika Rao| A | B | C | D | |
|---|---|---|---|---|
| 1 | Given | Middle | Family | Display name |
| 2 | Anika | Rao | Anika Rao | |
| 3 | Dev | Kumar | Shah | Dev Kumar Shah |
| 4 | Meera | Nair | Meera Nair |
=TEXTJOIN(" ",TRUE,A2:C2)Join Anika and Rao with one space, skipping the empty middle field.
- Select the three source name fields on the current row.
- Use an ordinary space as the delimiter.
- Set ignore_empty to TRUE when missing middle fields should not leave an empty position.
- Fill down and confirm Dev's supplied middle name remains included.
Preserve missing positions or change the delimiter
=TEXTJOIN("|",FALSE,A2:C2)The empty middle field produces two adjacent separators, visibly preserving the field positions.
=TEXTJOIN(", ",TRUE,A2:A4)Join three rows in their current order; repeated values would not be removed.
=TEXTJOIN("-",TRUE,"DEL","1042")Two supplied text items are joined with one hyphen.
Limits and compatibility
- ignore_empty does not trim text containing spaces. A whitespace-only field is still text and can add an unwanted-looking part.
- Numeric zero is an included value, not a missing field. Errors in inputs need handling rather than silently becoming a clean list.
- The output is one text string, not several cells. More than 32,767 characters exceeds the cell limit and gives #VALUE!.
- TEXTJOIN is available in Microsoft 365 and Excel 2019/2021/2024. It is not a function in perpetual Excel 2016; use compatible concatenation approaches when needed.
Common mistakes and where to use the examples
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.