TEXTJOIN combines text from multiple cells or values into one string, separated by a delimiter you choose. It can optionally ignore empty cells, making it far cleaner than joining with the ampersand operator.
=TEXTJOIN(delimiter, ignore_empty, text1, …)What does TEXTJOIN do?
TEXTJOIN is a text function that joins several pieces of text into one string, placing a separator such as a comma, space, or line break between each piece. You tell it what delimiter to use, whether to skip empty cells, and which cells or values to join. The result is a single text value that reads naturally.
Before TEXTJOIN, combining cells meant writing long formulas with ampersands and extra separators, and blank cells often left stray commas behind. TEXTJOIN solves both problems in one step: it inserts the delimiter only between actual values and can ignore blanks automatically. That makes it faster to build and easier to read.
Imagine you have a customer table with first name, last name, city, and email in separate columns. You need a single line for a mailing label or a report note. With TEXTJOIN you write one formula, point at the range, and get a clean, comma-separated string that skips any missing details.
When to use TEXTJOIN
- Building full names from separate first and last name columns.
- Creating comma-separated lists of products, tags, or attendees from a range.
- Producing mailing labels or address lines from street, city, and postcode fields.
- Combining report notes or comments from several cells into one summary cell.
- Joining values with a line break for display inside a single cell.
- Merging ID codes or reference numbers with a consistent separator.
TEXTJOIN syntax
=TEXTJOIN(delimiter, ignore_empty, text1, …)| Argument | Required? | What to enter |
|---|---|---|
delimiter | Required | The separator to place between each text item. Use text in quotes, such as ", " for a comma and space, or a cell reference that holds the separator. |
ignore_empty | Required | TRUE to skip empty cells, FALSE to include them (which can create double delimiters). |
text1 | Required | The first text item or range to join. You can supply a single cell, a range, or a text value. |
text2, … | Optional | Additional text items or ranges to join, up to 252 text arguments in total. |
What it returns: A single text string containing all non-empty text items joined by the delimiter. Numbers are converted to text automatically.
TEXTJOIN examples
1 Join first and last names with a space
The goal is to create a full name from two separate columns. The formula uses a space as the delimiter and sets ignore_empty to TRUE so a missing middle name or last name does not leave an extra space. It joins the value in A2 and the value in B2, returning the combined name as text. If either cell is blank, the result still reads cleanly.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | First Name | Last Name | |||
| 2 | Ava | Patel | Ava Patel | ||
| 3 | Liam | Chen | |||
| 4 | Noah | Garcia | |||
| 5 | Mia | Johnson | |||
| 6 |
2 Create a comma-separated product list from a range
Here you want one cell that lists all products in a range, separated by commas. The formula sets the delimiter to a comma and space, sets ignore_empty to TRUE, and points at the range A2:A6. TEXTJOIN walks through the range and places the delimiter only between actual values. The result is a tidy list such as Laptop, Monitor, Keyboard, Mouse, Webcam.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | |||
| 2 | Laptop | Laptop, Monitor, Keyboard, Mouse, Webcam | ||
| 3 | Monitor | |||
| 4 | Keyboard | |||
| 5 | Mouse | |||
| 6 | Webcam | |||
| 7 |
3 Build a mailing address line and skip blanks
The goal is a single address line from street, city, and postcode columns. The formula uses a comma and space as the delimiter and sets ignore_empty to TRUE. It joins A2, B2, and C2 in order. Because ignore_empty is TRUE, if the street field is blank the result starts with the city instead of a leading comma. The returned text is ready to paste into a label or email.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Street | City | Postcode | |||
| 2 | 12 Oak Lane | Bristol | BS1 4TY | 12 Oak Lane, Bristol, BS1 4TY | ||
| 3 | Leeds | LS1 2AB | ||||
| 4 | 5 Mill Road | York | YO1 7HH | |||
| 5 |
4 Join order details with a custom separator
You need a compact order summary that combines an order ID, customer name, and total. The formula uses a pipe character with spaces as the delimiter and sets ignore_empty to TRUE. It joins A2, B2, and C2, converting the number in C2 to text automatically. The result is a single string such as ORD-1001 | Ava Patel | 249.99, useful for logs or notes.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Order ID | Customer | Total | |||
| 2 | ORD-1001 | Ava Patel | 249.99 | ORD-1001 | Ava Patel | 249.99 | ||
| 3 | ORD-1002 | Liam Chen | 89.5 | |||
| 4 | ORD-1003 | Noah Garcia | 412 | |||
| 5 |
5 Combine a row of monthly figures into a report note
The goal is a short report note that lists quarterly sales figures in one cell. The formula uses a comma and space as the delimiter and sets ignore_empty to TRUE. It joins B2, C2, and D2, which hold the three monthly values. Numbers are converted to text, so the result reads like 1200, 1350, 1500. This is handy when you need a quick summary line without extra helper columns.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | |||
| 2 | North | 1200 | 1350 | 1500 | 1200, 1350, 1500 | ||
| 3 | South | 980 | 1100 | 1250 | |||
| 4 |
Common TEXTJOIN errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The delimiter argument is missing or the formula has too few arguments. | Supply both the delimiter and ignore_empty arguments before the text items, for example =TEXTJOIN(", ", TRUE, A2:A5). |
| #NAME? | The function name is misspelled or the workbook uses an Excel version that does not support TEXTJOIN. | Check the spelling and confirm you are using Excel 2019, Excel 2021, Microsoft 365, or Excel for the web. Older versions need a different approach. |
| #VALUE! | A text argument refers to a range that is too large or contains unsupported values. | Limit the range to the cells you actually need, or split the join into smaller TEXTJOIN formulas. |
| Extra commas or spaces | ignore_empty is set to FALSE, so empty cells still receive a delimiter. | Set the second argument to TRUE so blank cells are skipped. |
Tips for using TEXTJOIN
- Use TRUE for ignore_empty almost every time; it prevents stray commas and double spaces.
- To insert a line break inside the joined text, use CHAR(10) as the delimiter and turn on Wrap Text in the cell.
- Combine TEXTJOIN with IF to join only values that meet a condition, such as =TEXTJOIN(", ", TRUE, IF(A2:A10="North", B2:B10, "")).
- Remember that TEXTJOIN returns text, so numbers lose their number formatting; use TEXT inside the join if you need currency or date formats.
- Use a cell reference for the delimiter when you want to change the separator quickly without editing the formula.
- TEXTJOIN works with ranges, so you can join an entire column or row without listing each cell individually.
Frequently asked questions
What is the difference between TEXTJOIN and CONCATENATE?
CONCATENATE and the & operator join cells but require you to add separators manually and cannot skip blanks. TEXTJOIN adds a delimiter automatically and can ignore empty cells, so it is cleaner for lists and ranges.
Does TEXTJOIN work in older versions of Excel?
TEXTJOIN was introduced in Excel 2019 and is available in Excel 2021, Microsoft 365, and Excel for the web. Excel 2016 and earlier do not have it, so those users need alternative formulas.
How do I join text with a line break using TEXTJOIN?
Use CHAR(10) as the delimiter, for example =TEXTJOIN(CHAR(10), TRUE, A2:A5), and enable Wrap Text on the result cell so the line breaks display.
Can TEXTJOIN ignore blank cells?
Yes. Set the ignore_empty argument to TRUE and any empty cells in the range are skipped, so the delimiter appears only between actual values.
Why does TEXTJOIN return a number without formatting?
TEXTJOIN converts numbers to text using their underlying value, so currency symbols and date formats are lost. Wrap each value in the TEXT function to keep the format, for example TEXT(C2, "$#,##0.00").
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. TEXTJOIN reference