TEXTJOIN vs CONCATENATE vs CONCAT in Excel: What Differs

Three functions, three different jobs

CONCATENATE is the oldest. Its syntax is CONCATENATE(text1,[text2],...), and each item is a text value, a number or a cell reference. CONCAT is its replacement: CONCAT(text1,[text2],...) accepts ranges as well. TEXTJOIN is the newest: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...).

Microsoft's CONCAT page states that CONCAT has no delimiter or IgnoreEmpty argument, and points to TEXTJOIN for both. That one sentence is the core of the comparison.

Limits and versions side by side

The numbers below come from each function's own Microsoft page, read on 2026-11-01.

• CONCATENATE: up to 255 items and 8,192 characters in total. Listed for Microsoft 365, Excel 2024, 2021, 2019 and 2016.
• CONCAT: up to 253 text arguments. Over 32,767 characters returns #VALUE!. Listed for Microsoft 365, Excel 2024, 2021, 2019 and 2016.
• TEXTJOIN: up to 252 text arguments including text1. Over 32,767 characters returns #VALUE!. Listed for Microsoft 365, Excel 2024, 2021 and 2019, but not 2016.

Excel 2016 is the dividing line

If anyone who opens your file still uses Excel 2016, TEXTJOIN is not on Microsoft's list for that version. CONCATENATE is, and so is CONCAT. The CONCATENATE page says the function "may not be available in future versions of Excel", so use it only when an old file already depends on it.

A worked example: 6 cells in A2:A7, 2 of them empty

These are sample values. A2 holds red, A3 is empty, A4 holds green, A5 is empty, A6 holds blue and A7 holds black. You want one cell that reads "red, green, blue, black".

With TEXTJOIN: =TEXTJOIN(", ",TRUE,A2:A7). The result is red, green, blue, black, which is 4 items and 3 separators. With ignore_empty set to FALSE, the same formula returns red, , green, , blue, black with the 2 gaps kept. Microsoft's own example shows the same effect: a1, b1, a2, b2, , , a4 and so on.

With CONCAT: =CONCAT(A2:A7) returns redgreenblueblack. No comma, no space, and nothing you can switch on. To get separators you would write =A2&", "&A3&", "&A4 and so on, and the empty cells would produce stray commas.

Ranges: CONCAT and TEXTJOIN take them, CONCATENATE lists items

The documented syntax of CONCATENATE takes items, so to join 6 cells you list 6 references. CONCAT accepts =CONCAT(B2:C8) and even full columns such as =CONCAT(B:B, C:C). TEXTJOIN accepts a range for the text argument as well, and it can take a range as the delimiter: Microsoft's third example uses =TEXTJOIN(A8:D8, TRUE, A2:D7) to build rows split by commas and semicolons.

What counts as a delimiter in TEXTJOIN

The delimiter is a text string that is empty, one or more characters in double quotes, or a reference to a valid text string. A number is treated as text, so a delimiter of 1 would insert the digit between items. An empty delimiter makes TEXTJOIN behave like CONCAT, which Microsoft describes as effectively concatenating the ranges.

Both required arguments come before the text. A common slip is to write the range first, as in =TEXTJOIN(A2:A7,", ",TRUE). Excel reads the first argument as the delimiter, because the delimiter always comes first. The order is delimiter, ignore_empty, then up to 252 text items.

Common mistakes and the errors they cause

A missing comma between arguments gives a stray quotation mark: Microsoft shows =CONCATENATE("Hello ""World") returning Hello"World. A missing quotation mark gives #NAME?. Words that run together need a " " argument or a trailing space inside the text.

Numbers lose their format in a join. With 23.5 in A1, wrap it as TEXT(A1,"$0.00") to get $23.50 inside the result. And if the combined text passes 32,767 characters, TEXTJOIN and CONCAT return #VALUE!, because that is the cell limit.

The ampersand is Microsoft's own advice for short joins

The CONCATENATE page recommends the & operator: =A1&B1 returns the same as =CONCATENATE(A1,B1) and is often quicker and simpler. For two or three cells, that is the least typing. Once the join has a repeating separator or empty cells, TEXTJOIN is shorter and easier to read.

Microsoft's name sample shows the short case: with Andreas in B2 and Hauser in C2, =CONCAT(B2," ",C2) and =B2&" "&C2 both return Andreas Hauser. Neither needs ignore_empty, so TEXTJOIN adds nothing for a fixed pair of cells. It starts to pay off when the list grows, when a separator repeats, or when any cell may be empty, as in the 6-cell example above.

The reverse job: TEXTSPLIT

TEXTSPLIT is the inverse of TEXTJOIN. =TEXTSPLIT(A2,",") splits one cell on commas into columns, and it is available in Microsoft 365 and Excel 2024. If the joined list needs to go back into cells and you have Excel 2021 or older, Text to Columns on the Data tab does the same job.

Who each function suits

TEXTJOIN suits mailing lists, tag cells and any range with gaps, on Excel 2019 or later. CONCAT suits a plain join of a range with no separator. CONCATENATE suits only files shared with people on versions where you cannot be sure CONCAT exists, and the & operator covers that case too. If a formula is showing an error on your screen and the cause is not clear, Crade can read the screen and explain it.