TEXTJOIN and CONCAT in Excel
Last updated
TEXTJOIN joins texts and whole ranges with the same delimiter between each piece, and can skip empty cells: =TEXTJOIN(", ",TRUE,B2:D2) returns English, French. CONCAT joins ranges with nothing in between: =CONCAT(A2:C2) turns B, 12 and C into B12C.
TEXTJOIN and CONCAT syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) =CONCAT(text1, [text2], …)
- delimiter
- The text TEXTJOIN puts between the pieces, in double quotes:
", "," "or"; ". Use""for none. - ignore_empty
- TRUE skips empty cells, so you never get two delimiters in a row. FALSE keeps them.
- text1
- The first piece to join: a range such as
B2:D2, a single cell or typed text in quotes. Both functions accept ranges. - text2, …
- Optional. More pieces, joined in the order you list them. TEXTJOIN takes up to 252, CONCAT up to 253.
Practice TEXTJOIN and CONCAT here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Join a row with commas
Each person lists up to three languages in B:D. In E2, use TEXTJOIN to list the languages of row 2 separated by a comma and a space, skipping empty cells, then copy it down to E5.
| Name | Language 1 | Language 2 | Language 3 | Languages |
| Olivia Reed | English | French | ||
| James Carter | Spanish | |||
| Sofia Patel | German | English | Italian | |
| Liam Brooks | Portuguese | English |
How to join a range, step by step
- Click the cell for the result and type
=TEXTJOIN(. - Type the delimiter in double quotes, such as
", ", then a comma. - Type TRUE to skip empty cells, then a comma, and select the range to join, e.g.
B2:D2. - Close the bracket and press Enter. When you want no delimiter at all,
=CONCAT(B2:D2)is shorter.
TEXTJOIN and CONCAT examples
A list with commas
=TEXTJOIN(", ",TRUE,B2:D2)
Returns English, French when D2 is empty. Without TRUE you would get English, French, with a comma at the end.
Full name with an optional middle name
=TEXTJOIN(" ",TRUE,A2:C2)
Returns Olivia Grace Reed, and James Carter with a single space when there is no middle name.
A code from three columns
=CONCAT(A2:C2)
Returns B12C from aisle B, shelf 12 and level C. CONCAT takes the range at once and adds no delimiter.
A column into one cell
=TEXTJOIN("; ",TRUE,A2:A8)
Joins the email addresses of a column into one recipient list, skipping the empty rows.
Common TEXTJOIN and CONCAT problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| Double delimiters | ignore_empty is FALSE, so each empty cell still gets its delimiter: English, , French. | Set the second argument to TRUE: =TEXTJOIN(", ",TRUE,B2:D2). |
| Words run together | CONCAT adds nothing between the cells, so Olivia and Reed become OliviaReed. | Use TEXTJOIN with a delimiter, =TEXTJOIN(" ",TRUE,A2:B2), or add " " as its own argument in CONCAT. |
| A date turns into a number | Joining uses the value behind the cell, not its format, so a date shows as a number such as 45658. | Format it inside the formula with TEXT, e.g. TEXT(B2,"mm/dd/yyyy"). |
| #NAME? | TEXTJOIN and CONCAT need Excel 2019 or Microsoft 365. Older versions don’t know them. | In an older version, use CONCATENATE or the & operator, listing each cell. |
TEXTJOIN vs CONCAT vs CONCATENATE
CONCATENATE and the & operator, seen in the CONCATENATE lesson, join pieces you list one by one. CONCAT is their modern replacement: same job, but it also accepts a range such as A2:C2. TEXTJOIN goes one step further and puts the same delimiter between every piece, which is what you want for lists, names and addresses. CONCATENATE still works in every version, so files that must open in Excel 2016 or older should keep it.
Frequently asked questions
What is the difference between CONCAT and CONCATENATE?
CONCAT accepts whole ranges, CONCATENATE needs each cell listed separately. Otherwise they give the same result, and CONCATENATE is kept for compatibility with older files.
How do I join cells with a line break?
Use CHAR(10) as the delimiter, =TEXTJOIN(CHAR(10),TRUE,A2:A5), and turn on Wrap Text for the cell so the lines show.
Can TEXTJOIN skip empty cells?
Yes, that is its second argument. TRUE skips them, FALSE keeps a delimiter for each empty cell.
Is there a length limit?
A cell holds up to 32,767 characters. If the joined text is longer, TEXTJOIN returns #VALUE!.
Keep practising
All 5 TEXTJOIN and CONCAT exercises, with explanations and progress tracking.
Open the TEXTJOIN practice set