CONCATENATE in Excel
Last updated
CONCATENATE joins several pieces of text into one. For example, =CONCATENATE(A2," ",B2) joins the first name in A2 and the last name in B2 with a space between them. The & operator does the same: =A2&" "&B2. Spaces and punctuation go in double quotes.
CONCATENATE syntax
=CONCATENATE(text1, [text2], ...)
- text1
- The first piece to join: a cell reference (
A2), typed text in double quotes ("INV-") or a number. - text2, ...
- Optional. More pieces, joined in the order you list them, up to 255. Nothing is added between them, so type spaces and punctuation as their own pieces, like
" "or", ".
Practice CONCATENATE 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 first and last name
In C2, use CONCATENATE to join the first name in A2 and the last name in B2, with a space between them, to get Olivia Reed.
| First name | Last name | Full name |
| Olivia | Reed | |
| James | Carter | |
| Sofia | Patel | |
| Liam | Brooks | |
| Emma | Hughes | |
| Noah | Bennett |
How to use CONCATENATE, step by step
- Click the cell where you want the joined text and type
=CONCATENATE(. - Click the first cell to join, such as the first name, then type a comma.
- Type the separator in double quotes, such as
" "for a space, then a comma. - Click the next cell, add more pieces the same way if you need them, close the bracket and press Enter.
CONCATENATE examples
Join a first and last name
=CONCATENATE(A2," ",B2)
Returns Olivia Reed. The space in quotes is its own argument; without it you get OliviaReed.
The same join with &
=B2&", "&A2
Returns Reed, Olivia. & joins two pieces at a time, so chain as many as you need.
Build a product code
=A2&"-"&B2&"-"&C2
Returns LAP-14-BLK from three columns. Numbers are turned into text as they are joined, so copy the formula down to build every code.
Keep leading zeros in a number
="INV-"&TEXT(A2,"0000")
Returns INV-0007 for 7. A joined number keeps its plain look, so TEXT formats it with four digits first.
Common CONCATENATE problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| Words run together | CONCATENATE adds nothing between its arguments, so OliviaReed comes out with no space. | Add the separator as its own argument in quotes: =CONCATENATE(A2," ",B2). |
| The formula shows as text | The cell was formatted as Text before you typed the formula, or the formula doesn’t start with =. | Set the cell format to General, then press F2 and Enter to re-enter the formula. |
| Lost zeros or long decimals | A joined number uses its raw value, not the cell’s number format: 0007 becomes 7 and 12.5% becomes 0.125. | Format the number inside the formula with TEXT, e.g. TEXT(A2,"0000") or TEXT(B2,"0%"). |
| #NAME? | The function name is misspelled, or typed text is missing its double quotes, so Excel reads it as a name. | Check the spelling and put every piece of typed text, even a single space, in double quotes. |
CONCATENATE vs & vs CONCAT and TEXTJOIN
CONCATENATE and the & operator give the same result: =CONCATENATE(A2," ",B2) and =A2&" "&B2 both return Olivia Reed. & is shorter to type, CONCATENATE can be easier to read with many pieces. Newer versions of Excel add CONCAT, which also accepts whole ranges such as A2:C2, and TEXTJOIN, which puts the same separator between every piece and can skip empty cells. CONCATENATE still works in every version. The TEXTJOIN & CONCAT lesson practises both.
Frequently asked questions
How do I add a space between two cells?
Put a space in double quotes between them: =CONCATENATE(A2," ",B2) or =A2&" "&B2.
Is & the same as CONCATENATE?
Yes, for joining text they return the same result. & is an operator, so you write it between each pair of pieces instead of inside a function.
Why does my joined number lose its format?
Joining uses the number itself, not how the cell displays it. Wrap it in TEXT with the format you want, for example TEXT(A2,"0000") to keep leading zeros.
Can CONCATENATE join a whole range?
No, you list each cell separately. CONCAT and TEXTJOIN, available in newer versions of Excel, accept a range such as A2:C2.
Keep practising
All 5 CONCATENATE exercises, with explanations and progress tracking.
Open the CONCATENATE practice set