TEXTJOIN function
Quick answer
TEXTJOIN joins a list of values with the same delimiter between each one, and can skip blanks.
- Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, ...)- Most common use
=TEXTJOIN("; ",TRUE,A2:A4)returns maya@acme.com; raj@globex.io (Emails for one To: line, skipping the blank)
It turns a column of emails into one semicolon-separated line, or a row of tags into "red, large, cotton", without a chain of & signs.
- Available in
- Excel 2019 and later, including Microsoft 365
- Category
- Text functions
- Returns
- One text string, up to 32,767 characters.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, ...)
| Argument | Required? | What it is |
|---|---|---|
delimiter | Required | The text to put between items, such as ", " or "; ". Use "" for none. |
ignore_empty | Required | TRUE skips empty cells. FALSE keeps them, which leaves double delimiters. |
text1 | Required | The values or ranges to join. Add more after it, separated by commas. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =TEXTJOIN("; ",TRUE,A2:A4) | maya@acme.com; raj@globex.io | Emails for one To: line, skipping the blank |
| E3 | =TEXTJOIN(", ",TRUE,B3:D3) | blue, large, wool | A row of tags as one readable list |
| E4 | =TEXTJOIN(", ",FALSE,B2:D2) | red, , cotton | With FALSE, the empty tag leaves a double comma |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Tag 1 | Tag 2 | Tag 3 | Result | |
| maya@acme.com | red | cotton | maya@acme.com; raj@globex.io | |
| raj@globex.io | blue | large | wool | blue, large, wool |
| green | small | red, , cotton |
Practice TEXTJOIN
From lesson 19, Joining text. Graded instantly, then re-checked on changed data.
One-cell list
G2, list every city from C2:C6 in one cell, separated by a comma and a space, skipping blanks.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #NAME? in older versions | TEXTJOIN needs Excel 2019 or later. In older versions, join with & and handle blanks with IF. |
| Double delimiters | Set ignore_empty to TRUE. Cells where a formula returns "" are skipped too. |
| #VALUE! on long lists | The result can't exceed 32,767 characters, the most a cell can hold. |
Tips
- Combine it with FILTER to list only matching items: =TEXTJOIN(", ",TRUE,FILTER(A2:A20,B2:B20="East")).
- Use CHAR(10) as the delimiter and turn on Wrap Text for one item per line.
Questions
How do I combine a column into one cell with commas?
Use =TEXTJOIN(", ",TRUE,A2:A50). TRUE skips the blank cells.
What is the difference between TEXTJOIN and CONCAT?
TEXTJOIN puts a delimiter between items and can skip blanks. CONCAT runs everything together with nothing in between.
Lessons that use TEXTJOIN
- Lesson 19: Joining text (Pro)
Answers that use TEXTJOIN
- How to return all matches of a lookup in Excel
- How to return the column header where a value is found in Excel
- How to list the column headers of cells marked with an X in Excel
- How to find the row and column labels of the largest value in a table
- How to list all names tied for the highest value in Excel
In other languages
TEXTJOIN is UNIRCADENAS in Spanish Excel and TEXTVERKETTEN in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .