Comma-separated list
| A | |
|---|---|
| 1 | Name |
| 2 | Ana |
| 3 | |
| 4 | Cara |
| 5 | Dan |
Formula: C1: =TEXTJOIN(", ",TRUE,A2:A5)
Result: Ana, Cara, Dan
TRUE skips the empty cell A3, so there are no doubled commas.
Text
TEXTJOIN
Joins a list with a delimiter.
Syntax
TEXTJOIN(delimiter, ignore_empty, text1, …)Example: =TEXTJOIN(", ",TRUE,A2:A10)
Available in: Excel 2019+
TEXTJOIN combines the text from several cells or ranges with a delimiter between them: =TEXTJOIN(delimiter, ignore_empty, text1, …). It can skip blanks, which makes it better than CONCAT, CONCATENATE or & for building lists.
When to use it
- You turn a column of names into one comma-separated list.
- You join first and last name, or parts of an address, without double spaces when a part is missing.
- You build codes such as EU-Pen-2024 from separate pieces.
- You need a joined result from a whole range, which CONCATENATE cannot take.
Arguments explained
- delimiter. Text placed between every pair of joined items, such as ", " or "-". An empty string "" joins them with nothing between.
- ignore_empty. TRUE skips blank cells so you get no doubled delimiters. FALSE keeps them as empty items. TRUE is the usual choice.
- text1. The first text, cell or range to join. A range is read row by row.
- text2, …. Optional additional items or ranges, up to 252 arguments in total.
Worked examples
Full name
| A | B | |
|---|---|---|
| 1 | First | Last |
| 2 | Maria | Lopez |
Formula: D2: =TEXTJOIN(" ",TRUE,A2,B2)
Result: Maria Lopez
A single space is the delimiter.
Build a product code
| A | B | C | |
|---|---|---|---|
| 1 | Region | Product | Year |
| 2 | EU | Pen | 2024 |
Formula: E2: =TEXTJOIN("-",TRUE,A2:C2)
Result: EU-Pen-2024
Joins a row range with hyphens. A number such as 2024 is joined as is; use TEXT for leading zeros.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in D2: =TEXTJOIN(", ",TRUE,A2:A5)
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Text | Other | Result | |
| 2 | Alice Johnson | alice johnson | Alice Johnson, [email protected], extra spaces , SKU-2026-0415 | |
| 3 | [email protected] | [email protected] | ||
| 4 | extra spaces | |||
| 5 | SKU-2026-0415 | |||
| 6 | iced coffee, tea, sugar | |||
| 7 | Order 417 shipped |
Result in D2: Alice Johnson, [email protected], extra spaces , SKU-2026-0415
Common errors and how to fix them
- #NAME?. TEXTJOIN exists in Excel 2019 and Microsoft 365. Older versions need & or CONCATENATE.
- #VALUE!. The result is longer than 32,767 characters, the cell limit.
- Doubled delimiters. You set ignore_empty to FALSE. Use TRUE to skip blank cells.
- Numbers lose formatting. Numbers and dates are joined as raw values. Wrap them in TEXT(value,"format").
Related functions
Frequently asked questions
- What is the difference between TEXTJOIN and CONCAT?
- Both accept ranges, but TEXTJOIN adds a delimiter and can ignore empty cells. CONCAT joins everything directly.
- Can TEXTJOIN join only matching rows?
- Yes, combine it with FILTER: =TEXTJOIN(", ",TRUE,FILTER(B2:B20,A2:A20="East")).
- How do I add a line break between items?
- Use CHAR(10) as the delimiter and turn on Wrap Text.
- Is CONCATENATE obsolete?
- It still works but is kept for compatibility. CONCAT and TEXTJOIN replace it.