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.
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.
-
Join a whole range
How do I join every name in a column into one cell?
Excel shows Boxer cartridgeBriar pipeCandleB1Item2Boxer cartridge3Briar pipe4CandleItemsGive 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.
-
Put a comma after each item
How do I separate the items with commas?
Excel shows Boxer cartridge, Briar pipe, Candle,B1Item2Boxer cartridge3Briar pipe4CandleItemsJoin ", " 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
-
-
Keep a currency format
How do I show the fare as $1.75?
Excel shows Fare: $1.75F1Cab Fare2$0.003$1.75PlacesCONCAT 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
-
-
Use the & sign
Can I join text without a function?
Excel shows Scotland Yard, LondonBD1PlaceRegion2221B Baker StreetLondon3Scotland YardLondonPlacesThe 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.
-
Add a line break
How do I put the role on a new line under the name?
Excel shows Sherlock Holmes DetectiveBC1NameRole2Sherlock HolmesDetectivePeopleCHAR(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!D3Scotland 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 .