Excel function guide · Text
CONCAT and TEXTJOIN in Excel
CONCAT joins text from cells and strings into one, for example a first and last name with a space between. TEXTJOIN does the same with a delimiter you choose, such as a comma, and can skip empty cells.
Syntax
=CONCAT(text1, [text2], …)
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
=A2&" "&B2
CONCAT and TEXTJOIN are in Excel for Microsoft 365, Excel 2019 and later, and Excel for the web. The & operator and the older CONCATENATE function work in every version.
| Argument | Required? | What it means |
|---|---|---|
| text1, text2, … (CONCAT) | Required | Cells, ranges or text in quotes to join, in order. |
| delimiter (TEXTJOIN) | Required | What to put between items, e.g. ", ". Use "" for nothing. |
| ignore_empty (TEXTJOIN) | Required | TRUE skips empty cells; FALSE keeps them, leaving doubled delimiters. |
| text1, … (TEXTJOIN) | Required | The cells or ranges to join. |
CONCAT and TEXTJOIN examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | |
|---|---|---|---|
| 1 | First name | Last name | City |
| 2 | Sara | Ahmed | Riyadh |
| 3 | Tom | Baker | Leeds |
| 4 | Lina | Costa | |
| 5 | Yusuf | Demir | Izmir |
1. First and last name
=CONCAT(A2, " ", B2)
Result: Sara Ahmed
The " " in the middle adds the space. =A2&" "&B2 gives the same result.
2. A list, skipping blanks
=TEXTJOIN(", ", TRUE, C2:C5)Result: Riyadh, Leeds, Izmir
TEXTJOIN puts the delimiter between the cities and skips Lina's empty cell.
3. Keeping blanks
=TEXTJOIN(", ", FALSE, C2:C5)Result: Riyadh, Leeds, , Izmir
With ignore_empty FALSE, the empty cell still gets a delimiter, so you see two commas in a row.
Try it now
Practise CONCAT and TEXTJOIN in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In D2, join the first and last name with a space between them, using CONCAT (or &).
Common CONCAT and TEXTJOIN mistakes
Forgetting the space
=CONCAT(A2, B2) gives SaraAhmed. Spaces and punctuation are text too: CONCAT(A2, " ", B2).
Numbers lose their format
1250.5 joins as 1250.5, not 1,250.50. Wrap the number in TEXT: TEXT(B2, "#,##0.00").
Dates turn into numbers
A date joins as its serial number, such as 46037. Use TEXT(C2, "dd mmm yyyy") inside the join.
CONCATENATE with a range
The older CONCATENATE only accepts single cells. CONCAT and TEXTJOIN accept ranges like C2:C5.
Expecting a number back
The result is always text. Joined digits won't add up in SUM until you convert them with VALUE.
Practise this in Excel Quest
Build text: &, CONCAT, TEXTJOIN & TEXT Members
Course 7 · Text & Data Cleaning · 12 min
Create full names, email addresses, padded employee codes and readable summary sentences from separate fields.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.