To concatenate in Excel, type a formula such as =CONCAT(A2," ",B2) to join the text in A2 and B2 with a space between them, or use the ampersand instead: =A2&" "&B2. The older CONCATENATE function still works, but Microsoft now recommends CONCAT.
Exported client lists love to split things up: first name in one column, surname in another, house number in a third. For a mail merge or a label you need them joined back together, and that's what concatenating means. Excel gives you three ways to do it, and they all produce the same result once you remember the spaces.
Joining first and last names with CONCAT
Say A2 holds Sarah and B2 holds Jones. Here's how to get Sarah Jones in C2:
- Click cell C2.
- Type =CONCAT(A2," ",B2) and press Enter. The " " in the middle is a space wrapped in quote marks.
- Drag the small square at the bottom right of C2 down to copy the formula to the rest of your list.
- If you want plain text rather than formulas, copy column C and use Paste Special, Values.
Microsoft's own example does exactly this with CONCATENATE: =CONCATENATE(B2," ",C2) joins the text in B2, a space and the text in C2.
CONCAT, CONCATENATE or the ampersand?
| Option | Example | Good to know |
|---|---|---|
| CONCAT | =CONCAT(A2," ",B2) | The current function; it can also take a whole range |
| CONCATENATE | =CONCATENATE(A2," ",B2) | Older; kept for compatibility |
| Ampersand | =A2&" "&B2 | No function needed; quick for two or three cells |
| TEXTJOIN | =TEXTJOIN(", ",TRUE,A2:C2) | Adds a separator and can skip blanks |
Microsoft says CONCATENATE has been replaced by CONCAT and may not be available in future versions, though it's still there for older workbooks. I'd use CONCAT or the ampersand for anything new. The ampersand is the one I reach for most, because there's no function name to remember.
Why your joined text runs together
This is the mistake everyone makes once. Excel joins exactly what you give it, nothing more. Without a space between the items you get SarahJones. Microsoft's fix is to add a space in quote marks as its own item, or to put a space inside the text you're adding, like "Order for ".
Two other errors are worth knowing. Microsoft says a #NAME? error usually means you've missed the quote marks around a piece of text. And if quote marks show up in your result, you've probably left out a comma between two text items.
Adding your own words to the result
You can mix cell references with your own text to build useful labels. For a delivery sheet with the client in A2 and the order number in B2, try =CONCAT("Order ",B2," for ",A2) to get something like Order 7 for Sarah Jones. Notice the spaces inside the quote marks before and after the words.
Numbers join fine, but they lose their formatting. A date in a cell comes through as a long serial number rather than the date you expected. Wrap it in the TEXT function first, for example =CONCAT(A2," ",TEXT(B2,"dd/mm/yyyy")), to keep it looking like a date.
Joining a whole row or range at once
CONCAT can take a range, which CONCATENATE can't. =CONCAT(A2:C2) joins all three cells in one go, though without any separators. If you want commas between them and want blank cells skipped, Microsoft points you to TEXTJOIN instead: =TEXTJOIN(", ",TRUE,A2:C2) turns a split address into one tidy line.
There are limits. Microsoft says CONCATENATE accepts up to 255 items, CONCAT up to 253 text arguments, and a single cell can only hold 32767 characters before CONCAT returns #VALUE!. You won't hit those joining names, but you might if you're stitching together long product descriptions.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Turning joined text into plain values
A joined column is still made of formulas, so if you delete the original first and last name columns, the joined names vanish too. Copy the result, then right-click and choose Paste Values, and they'll stay put. If your list also needs a quick sum split across rows, my guide to dividing in Excel uses the same drag-to-fill trick.
If you'd like more ways to merge data, my guide to combining two columns in Excel compares the options side by side. It also means you can share the practice file with a colleague without a second thought.
Splitting text back apart
Sometimes you need the reverse: one column holding full names that you want split into first and last. Concatenation won't help there. The quickest route is Flash Fill: type the first name for the first client in the next column, press Enter, start typing the second, and Excel offers to fill the rest from the pattern. Ctrl+E runs it directly, and Data, Flash Fill does the same from the ribbon if you'd rather click.
I'd keep both versions while you check the results. Hide the original columns rather than deleting them until you're sure every row joined or split the way you wanted. Odd entries, like a client with a middle name or a company name in the surname column, are where these tricks go wrong, and it's far easier to spot them side by side.
My verdict
Use the ampersand for quick jobs and CONCAT for anything longer, and always put a space in quote marks between names. If you're joining addresses with lots of blank cells, skip both and use TEXTJOIN. It's one of those formulas that takes two minutes to learn and saves you hours of retyping.
Frequently asked questions
What is the difference between CONCAT and CONCATENATE?
CONCAT is the newer function and can join a whole range. CONCATENATE is older and only kept for compatibility.
How do I add a space when concatenating in Excel?
Put a space in quote marks as its own item, for example =A2&" "&B2.
Why do I get #NAME? when I concatenate?
Microsoft says it usually means quote marks are missing around a piece of text in the formula.
Can I concatenate with a comma between each cell?
Yes. Use TEXTJOIN with a comma as the separator, or add ", " between each item in CONCAT.
