Joining text: &, CONCAT and TEXTJOIN
The & sign joins text together: =A2&" "&B2 turns Grace and Reid into Grace Reid. CONCAT does the same as a function, and TEXTJOIN joins a whole range with a separator such as a comma.
A worked example
A staff list has first names in column A and surnames in column B. You want full names in column C.
Join Grace and Reid directly and you get GraceReid, because the spreadsheet only joins exactly what you give it. You need to add the space yourself: Grace, then a space, then Reid.
=A2&" "&B2
A2is the first name." "is a space. Any text you type in, including a space, goes in double quotes.B2is the surname. Each&joins the next piece on.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | First name | Surname | Full name | |
| 2 | Grace | Reid | Grace Reid | grace.reid@company.co.uk |
| 3 | Tom | Fraser | Tom Fraser | tom.fraser@company.co.uk |
| 4 | Priya | Shah | Priya Shah | priya.shah@company.co.uk |
CONCAT and TEXTJOIN
CONCAT is the same as &, written as a function: =CONCAT(A2," ",B2). Use whichever you find easier to read. (Older versions of Excel call it CONCATENATE.)
TEXTJOIN is handy when you're joining lots of cells with the same separator between each one:
=TEXTJOIN(", ",TRUE,A2:A6)
This gives a list such as Grace, Tom, Priya. The first part is the separator, and TRUE means skip empty cells so you don't get ", ," in the middle.
Joining numbers and dates
Joining a date gives a strange number, because underneath, a date is a count of days. "Starts on "&C2 might show Starts on 46128. Use TEXT to say how it should look:
="Starts on "&TEXT(C2,"dd/mm/yyyy")
Money is the same: TEXT(D2,"£#,##0.00") shows 1234.5 as £1,234.50.
Common mistakes
No space between the names
=A2&B2 gives GraceReid. Add " " between them.
Using + instead of &
=A2+" "+B2 tries to add the text up as numbers and gives #VALUE!. Use &.
Forgetting the quotes
Text you type into a formula, even a single comma or space, needs double quotes: ", ".
Extra spaces from the data
If a name already ends with a space you'll get two in a row. Wrap it in TRIM: =TRIM(A2&" "&B2).
Try it now
In C2, join each first name and surname into a full name, with a space between them, such as Grace Reid. Your formula will be copied down to C7.
| A | B | C | |
|---|---|---|---|
| 1 | First name | Surname | Full name |
| 2 | Grace | Reid | |
| 3 | Tom | Fraser | |
| 4 | Priya | Shah | |
| 5 | Marek | Nowak | |
| 6 | Nia | Evans | |
| 7 | Ollie | Morgan |
Where it works
& works everywhere. CONCAT and TEXTJOIN work in Excel 2019 onwards, Excel for Microsoft 365, Google Sheets and LibreOffice Calc.