The CONCATENATE and CONCAT functions in Excel
Joining text is called concatenating. There are three ways to do it in Excel: the & operator (=A2&" "&B2), the classic CONCATENATE function, and its newer replacement CONCAT, which also accepts whole ranges.
They all glue text together exactly as it is, so you add spaces, commas or other separators yourself. To join a long range with a separator, TEXTJOIN is easier.
Syntax
=CONCATENATE(text1, [text2], …)
| Argument | What it means |
|---|---|
| text1 | The first text, cell or number. |
| text2, …optional | More items, joined in order. CONCAT takes the same arguments but also accepts ranges such as A2:C2. |
Examples
1. First and last name
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | First | Last | Order | Full name | |
| 2 | Maya | Cohen | 1042 | Maya Cohen | |
| 3 | Omar | Haddad | 1043 |
The " " in the middle adds the space. =A2&" "&B2 does exactly the same.
2. Text and a number
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | First | Last | Order | Label | |
| 2 | Maya | Cohen | 1042 | ||
| 3 | Omar | Haddad | 1043 | Order 1043 |
Numbers are turned into text automatically. Mind the space at the end of "Order ".
3. A whole range: CONCAT
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | First | Last | Order | Code | |
| 2 | Maya | Cohen | 1042 | MayaCohen1042 | |
| 3 | Omar | Haddad | 1043 |
CONCAT joins every cell of the range, with no separator. For separators, use TEXTJOIN.
Common mistakes
Missing spaces, e.g.
=A2&B2gives "MayaCohen".Fix: Add the space as its own piece:
=A2&" "&B2.Dates turn into numbers, e.g. "Due 46099".
Fix: Format the date with TEXT:
="Due "&TEXT(B2,"yyyy-mm-dd").
Frequently asked questions
Should I use CONCATENATE or CONCAT?
CONCAT in Excel 2019 and newer; CONCATENATE still works everywhere and is kept for compatibility. Many people simply use &.
How do I split a name back into first and last name?
Use TEXTBEFORE and TEXTAFTER with " " in Excel 365, or LEFT and FIND in older versions.