Skip to content

Excel function · Text

Excel CONCAT function

CONCAT joins the cells, ranges and texts you give it into one text, with nothing in between. It is the newer replacement for CONCATENATE and also accepts ranges.

In Turkish Excel: ARALIKBİRLEŞTİR

What it does

CONCAT arrived with Excel 2019 and Microsoft 365 as an improved CONCATENATE. The key difference is that it accepts ranges: =CONCAT(A2:D2) joins four cells from left to right. With a range of several rows, cells are read row by row, left to right within each row.

It does not add delimiters. When joining single cells you can add a delimiter as a separate piece (=CONCAT(A2,"-",B2)), but the way to put a delimiter between every cell of a range is TEXTJOIN. That is why CONCAT is used mostly where the pieces belong right next to each other, such as rebuilding a code that was split across columns.

Empty cells add nothing to the result. Numbers are joined without formatting; convert numbers that must keep their look with TEXT. If the file will be opened in Excel 2016 or earlier, CONCAT will not work there; use CONCATENATE instead.

Syntax

English Excel=CONCAT(text1, [text2], ...)
Turkish Excel=ARALIKBİRLEŞTİR(metin1; [metin2]; ...)

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
text1RequiredThe first piece to join: a cell, a range, text in quotes or a number.
text2OptionalFurther pieces; each can be a cell, a range or text. Up to 253 pieces.

Examples

1Rebuild a code split across columns

The parts of the product code are in three columns. Column D joins the range directly; column E lists the cells one by one, with the hyphens as separate pieces.

D2fx=CONCAT(A2:C2)
Rebuild a code split across columns: example table
ABCDE
1GroupSizeColourCode (joined)Code (hyphens)
2KBL1525SYKBL1525SYKBL-1525-SY
3PRZ0210BYPRZ0210BYPRZ-0210-BY
4SGT0016BYSGT0016BYSGT-0016-BY
5KNL2040GRKNL2040GRKNL-2040-GR
  • D2 (filled down to D5)=CONCAT(A2:C2)
  • E2 (filled down to E5)=CONCAT(A2,"-",B2,"-",C2)

D2 shows KBL1525SY and E2 shows KBL-1525-SY. The sizes are stored as text, so PRZ-0210-BY in E3 keeps its leading zero.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2CONCAT versus TEXTJOIN

The same list of sizes is joined in two ways. CONCAT writes the values next to each other; TEXTJOIN puts a comma and a space between them.

C2fx=CONCAT(A2:A5)
CONCAT versus TEXTJOIN: example table
ABCD
1SizeCONCATTEXTJOIN
2SSMLXLS, M, L, XL
3M
4L
5XL
  • C2=CONCAT(A2:A5)
  • D2=TEXTJOIN(", ",TRUE,A2:A5)

C2 shows SMLXL and D2 shows S, M, L, XL. To show the sizes as a readable list, you need TEXTJOIN.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#NAME?

Cause: The file was opened in Excel 2016 or earlier, which does not know CONCAT.

Fix: Use CONCATENATE or the & operator in those files.

#VALUE!

Cause: The joined text exceeds 32,767 characters, the most a cell can hold.

Fix: Join a smaller range, or split the result over several cells.

Unreadable result

Cause: The values in the range are joined with nothing in between, so "S", "M", "L" becomes "SML".

Fix: If you need a delimiter, use TEXTJOIN.

Good to know

  • Store code parts as text to keep leading zeros; a part stored as the number 210 is joined as 210, not 0210.
  • To split the joined text into parts again, use TEXTSPLIT in Microsoft 365.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Join the Group, Size and Colour columns into the full product code in a new column

Convert to table

Bring your list into the tool and type this. Nexell builds the full code with a real Excel join formula and shows it in the preview; in the downloaded file, the code changes when any part changes.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between CONCAT and CONCATENATE?

Both join pieces with nothing in between. CONCAT accepts ranges (=CONCAT(A2:D2)), while CONCATENATE needs every cell listed separately. CONCATENATE also works in older versions.

How do I add commas between values with CONCAT?

Not for a range; CONCAT has no delimiter argument. Use =TEXTJOIN(", ",TRUE,A2:A10) instead.

Related functions

All text functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free