CONCATENATE function
Concatenate means join. CONCATENATE joins text and values from several cells into one piece of text, in the order you list them.
Example
Read it as a sentence: take the name, add a space and an opening bracket, add the role, then close the bracket.
How CONCATENATE works
CONCATENATE builds one piece of text out of several. A full name from a first and last name, a label from a word and a number, a code from two IDs.
Each argument is one piece, and the pieces are joined in the order you give them. Nothing is added between them: a space, comma or bracket is a piece of its own, typed in double quotes.
In the example above there are four pieces: Sherlock Holmes from B2, then " (", then Detective from C2, then ")". Together they read Sherlock Holmes (Detective).
Numbers and dates join as plain digits, and an empty cell adds nothing. CONCATENATE is the older of Excel's two joining functions and is kept so old workbooks still work; CONCAT is the newer one.
Syntax
CONCATENATE(text1, [text2], ...)
Arguments in [square brackets] are optional.
- The first piece
text1(required) - First item to join.
- The next pieces
text2(optional) - More items, up to 255 in total.
Returns: One text string made of all the items joined end to end.
More examples
Each picture shows only the columns that formula reads.
-
Add a space or a dash
How do I put a separator between two cells?
Excel shows Briar pipe - SmokingBC1ItemCategory2Boxer cartridgeTools3Briar pipeSmokingItemsType the separator as its own piece, in quotes: " - " puts a spaced dash between the item and its category. Leave it out and the words run together, as Sherlock HolmesDetective shows.
-
=CONCATENATE(People!B2,People!C2)Sherlock HolmesDetective -
=CONCATENATE(People!B2," ",People!C2)Sherlock Holmes Detective
-
-
Use the & sign instead
Is there a shorter way to write it?
Excel shows Sherlock Holmes (Detective)BC1NameRole2Sherlock HolmesDetectivePeopleThe ampersand joins the same pieces without a function around them. This gives exactly the same text as the example at the top: Sherlock Holmes (Detective).
-
Keep the currency format
How do I show 3.50 as $3.50 inside the text?
Excel shows Unit value: $3.50E1Unit Value2$3.50ItemsCONCATENATE takes the stored number, 3.5, not what the cell shows. Pass it through TEXT with a format code such as "$0.00" and the dollar sign and both decimals come back.
-
=CONCATENATE("Unit value: ",Items!E2)Unit value: 3.5
-
-
Join a date
How do I join a date without getting a long number?
Excel shows Acquired Nov 2, 2024H1Acquired245598ItemsA date is stored as a serial number, so joining H2 on its own gives Acquired 45598. TEXT with "mmm d, yyyy" turns it back into Nov 2, 2024 first.
-
=CONCATENATE("Acquired ",Items!H2)Acquired 45598
-
-
Build a code from two cells
How do I make one ID out of an item and a place?
Excel shows I01-L01AG1Item IDKept At2I01L01ItemsJoin the item ID, a dash and the place ID: I01 and L01 become I01-L01. Fill it down and each row gets its own code.
CONCATENATE or CONCAT?
Give CONCATENATE a range, such as Items!B2:B4, and it does not join it. In current Excel it spills the three names into three cells, one each. CONCAT, the newer function, joins the same range into one cell: Boxer cartridgeBriar pipeCandle.
They also differ at the length limit. A result longer than 32,767 characters stops at 32,767 with CONCATENATE, where CONCAT returns #CALC!.
For a new workbook in Excel 2019 or later, CONCAT does everything CONCATENATE does and accepts ranges too. CONCATENATE is still worth knowing because it is all over older files.
Watch out for
A range spills instead of joining
CONCATENATE(Items!B2:B4) looks like it should join three names. Instead each cell of the range is handled on its own, and the three names land in three cells below the formula. List the cells one by one, or switch to CONCAT.
-
=CONCATENATE(Items!B2:B4)Boxer cartridge
Formats are lost
The cell shows $3.50 or Nov 2, 2024, but CONCATENATE joins the value underneath: 3.5, or the serial number 45598. Wrap the number or date in TEXT to choose how it looks.
-
=CONCATENATE("Unit value: ",Items!E2)Unit value: 3.5 -
=CONCATENATE("Acquired ",Items!H2)Acquired 45598
An empty cell leaves a dangling separator
Moriarty's Home cell is empty, so " lives at " is followed by nothing and the text stops mid-sentence. Check for blanks, or use TEXTJOIN, which can skip them.
-
=CONCATENATE(People!B10," lives at ",People!D10)James Moriarty lives at
Joined numbers come back as text
Joining the quantities 12 and 7 gives 127, but as text, not as a number. ISTEXT on the same join with CONCAT returns TRUE.
-
=CONCATENATE(Items!D2,Items!D3)127 -
=ISTEXT(CONCAT(Items!D2,Items!D3))TRUE
An error in any piece spreads
If one argument is #N/A, the whole result is #N/A and none of the other text survives.
-
=CONCATENATE("a",NA())#N/A
Questions people ask
How do you use CONCATENATE in Excel?
List the pieces in the order you want them, with any spaces or punctuation in quotes. A name, a space and a role gives Sherlock Holmes Detective.
-
=CONCATENATE(People!B2," ",People!C2)Sherlock Holmes Detective
What is an example of concatenate?
Joining a first and last name into one cell, or two IDs into one code: I01 and L01 joined with a dash give I01-L01.
-
=CONCATENATE(Items!A2,"-",Items!G2)I01-L01
Does CONCATENATE add spaces between words?
No. It joins the pieces exactly as they are, so a space has to be its own piece in quotes.
-
=CONCATENATE(People!B2,People!C2)Sherlock HolmesDetective -
=CONCATENATE(People!B2," ",People!C2)Sherlock Holmes Detective
Where it works
- Excel
- CONCATENATE is in every version of Excel from 2007 on. Compatibility function: still works in every version, but newer replacements exist.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Compatibility functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .