Practice free

CONCAT function

CONCAT joins text from several cells or values into one string and, unlike CONCATENATE, accepts whole ranges. It adds no separator, so use TEXTJOIN when you need one.

Example

Read it as a sentence: join B2, then a space and "(", then C2, then ")", in that order.

Practice this in the app

How CONCAT works

CONCAT puts text together. Use it for labels, full names, addresses on one line, or one cell that lists everything in a column.

It takes the pieces in order and adds nothing between them, so spaces and punctuation go in as pieces of their own, in quotes. In the example above, the name, " (", the role and ")" become Sherlock Holmes (Detective).

Unlike the older CONCATENATE, CONCAT also accepts a whole range and joins every cell in it. A block of several columns is read across the first row, then across the next, and so on down.

Empty cells add nothing, and what comes back is text, even when every piece was a number.

Syntax

CONCAT(text1, [text2], ...)

Arguments in [square brackets] are optional.

The first piece or range text1 (required)
The first text, cell, or range to join.
More pieces text2 (optional)
More items to join; ranges are allowed.

Returns: One joined text string.

More examples

Each picture shows only the columns that formula reads.

  1. Join a whole range

    How do I join every name in a column into one cell?

    Give CONCAT the range instead of each cell. B2:B4 holds three item names, and they come back as one run of text with nothing between them.

  2. Put a comma after each item

    How do I separate the items with commas?

    Join ", " to the range inside CONCAT and every name gets a comma after it, the last one too. When you only want commas between items, TEXTJOIN does that and can skip empty cells as well.

    • =TEXTJOIN(", ",TRUE,Items!B2:B4) Boxer cartridge, Briar pipe, Candle
  3. Keep a currency format

    How do I show the fare as $1.75?

    CONCAT reads the stored value, so the fare joins as 1.75. TEXT with "$0.00" sets how the number appears before it is joined.

    • =CONCAT("Fare: ",Places!F3) Fare: 1.75
  4. Use the & sign

    Can I join text without a function?

    The ampersand joins two pieces at a time. Place name, comma and space, region: Scotland Yard, London. For a long list of pieces or a range, CONCAT is easier to read.

  5. Add a line break

    How do I put the role on a new line under the name?

    CHAR(10) is the line-break character. Joined between the name and the role, it puts a break in the text after Sherlock Holmes. The cell still shows one line until you turn on Wrap Text for it; then Detective sits on the line below.

CONCAT or TEXTJOIN?

CONCAT adds nothing between the pieces. Joining a separator to a range leaves it after the last item too: Boxer cartridge, Briar pipe, Candle, with a comma at the end. TEXTJOIN takes the separator as its first argument and puts it only between items, and its second argument can skip blank cells.

Use CONCAT when you are building a label from a few known pieces. Use TEXTJOIN when you are listing the contents of a range.

Compared with CONCATENATE, CONCAT accepts ranges. CONCATENATE spills a range into separate cells, one name each, where CONCAT joins it into one.

Watch out for

Over 32,767 characters gives #CALC!

When the joined text would run past 32,767 characters, CONCAT returns #CALC! instead of a result. CONCATENATE behaves differently here: it cuts the text off at 32,767.

  • =CONCAT(REPT("a",32767),"b") #CALC!
  • =LEN(CONCATENATE(REPT("a",32767),"b")) 32767

A block joins row by row

Give CONCAT the two-column block A2:B3 and it reads the first row left to right, then the second: I01Boxer cartridgeI02Briar pipe. If you want one column after the other, join two single-column ranges instead.

  • =CONCAT(Items!A2:B3) I01Boxer cartridgeI02Briar pipe

Dates and currency lose their format

Holmes joined on March 4, 2019, but CONCAT joins the serial number behind the date and returns Joined 43528. TEXT with "mmmm yyyy" gives Joined March 2019.

  • =CONCAT("Joined ",People!G2) Joined 43528
  • =CONCAT("Joined ",TEXT(People!G2,"mmmm yyyy")) Joined March 2019

Numbers become text

12 and 7 join to 127, and ISTEXT confirms the result is text, not the number 127.

  • =CONCAT(Items!D2,Items!D3) 127
  • =ISTEXT(CONCAT(Items!D2,Items!D3)) TRUE

An error anywhere spreads

If any cell CONCAT reads holds an error, such as #DIV/0!, the result is that error.

  • =CONCAT(Data!F5) #DIV/0!

Questions people ask

What is the difference between CONCAT and TEXTJOIN?

TEXTJOIN puts a separator between items and can skip blank cells; CONCAT adds nothing between them. With a comma joined to each item, CONCAT leaves one at the end.

  • =TEXTJOIN(", ",TRUE,Items!B2:B4) Boxer cartridge, Briar pipe, Candle
  • =CONCAT(Items!B2:B4&", ") Boxer cartridge, Briar pipe, Candle,
How do I combine two columns in Excel?

Join the two cells of the first row, with a space in quotes between them, and fill the formula down. Given both columns as one range, CONCAT reads them row by row into a single cell.

  • =CONCAT(Items!A2:B3) I01Boxer cartridgeI02Briar pipe
How do I join values in Excel?

Use =CONCAT(value1, value2, …) or the & operator. Both join the pieces in order with nothing added.

  • =Places!B3&", "&Places!D3 Scotland Yard, London
What replaced CONCATENATE in Excel?

CONCAT, from Excel 2019, and TEXTJOIN for lists with a separator. CONCATENATE still works, so older files keep working.

  • =CONCAT(Items!B2:B4) Boxer cartridgeBriar pipeCandle

Where it works

Excel
CONCAT arrived in Excel 2019. Older versions do not have it.
Google Sheets
Same name, but behaves differently. Sheets CONCAT takes only two arguments; use CONCATENATE or the & operator for more.

Part of Text functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .