Practice free

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.

Practice this in the app

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.

  1. Add a space or a dash

    How do I put a separator between two cells?

    Type 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
  2. Use the & sign instead

    Is there a shorter way to write it?

    The 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).

  3. Keep the currency format

    How do I show 3.50 as $3.50 inside the text?

    CONCATENATE 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
  4. Join a date

    How do I join a date without getting a long number?

    A 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
  5. Build a code from two cells

    How do I make one ID out of an item and a place?

    Join 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 .