CONCATENATE vs TEXTJOIN
Concatenate vs TEXTJOIN in Excel and Google Sheets: compare syntax, delimiters, and empty-cell handling to pick the right text-joining function every time.
Introduction
Excel has three ways to glue text together: the legacy CONCATENATE, its modern replacement CONCAT, and the delimiter-aware TEXTJOIN. They look interchangeable until you need to join a whole range, insert commas between values, or skip blanks. This comparison shows exactly where each one wins so you stop fighting your join formulas.
Prerequisites
- Basic cell references (A1, A1:A10)
- Excel 2019/Microsoft 365 or Google Sheets for TEXTJOIN and CONCAT
1The Three Join Functions at a Glance
CONCATENATE(text1, text2, ...) accepts individual values only — you must list every cell by hand and it ignores range references as ranges. CONCAT(text1, ...) is its Excel 2019+ successor and DOES accept ranges, but neither of them supports a delimiter. TEXTJOIN(delimiter, ignore_empty, text1, ...) accepts ranges, inserts a separator between every item, and can skip empty cells. All three exist in Google Sheets with the same behavior; in Google Sheets CONCAT is limited to exactly two arguments, so prefer CONCATENATE or TEXTJOIN there.
Example
=CONCATENATE(A2, " ", B2) vs =CONCAT(A2:B2) vs =TEXTJOIN(" ", TRUE, A2:B2)For two cells the output is identical — but only TEXTJOIN adds the space automatically. CONCAT(A2:B2) returns "JohnSmith" with no separator, and CONCATENATE needs the " " typed in manually.
The & operator (=A2&" "&B2) is equivalent to CONCATENATE and works in every version.
Google Sheets difference: CONCAT(a, b) takes only 2 arguments there, unlike Excel's CONCAT which takes many.
2Where CONCATENATE Falls Apart: Joining a Range
Suppose A2:A6 holds five product codes and you want them in one cell separated by commas. With CONCATENATE you must reference every cell AND type every comma — five cells is tedious, fifty is unworkable. TEXTJOIN collapses the whole job into one short formula.
The painful legacy way
Reference each cell and each separator manually.
=CONCATENATE(A2, ", ", A3, ", ", A4, ", ", A5, ", ", A6)The TEXTJOIN way
Pass the delimiter once and the whole range.
=TEXTJOIN(", ", TRUE, A2:A6)Example
=TEXTJOIN(", ", TRUE, A2:A6)TEXTJOIN inserts ", " between every non-empty value in A2:A6. One formula handles 5 cells or 5,000 — just widen the range.
TEXTJOIN's delimiter can be any string: " | ", CHAR(10) for line breaks (turn on Wrap Text), or even "" for no separator.
3Empty Cells: TEXTJOIN's Killer Feature
The second argument of TEXTJOIN, ignore_empty, decides what happens with blanks. With TRUE, empty cells are skipped so you never get doubled delimiters like "Smith, , TX". CONCATENATE and CONCAT have no such option — blanks silently produce ugly output. This matters constantly when building address lines or full names where a middle name or address line 2 may be missing.
Example
=TEXTJOIN(", ", TRUE, A2, B2, C2)With ignore_empty = TRUE the empty B2 is dropped, so you get one clean comma. The same data with =CONCATENATE(A2, ", ", B2, ", ", C2) returns "Anna Lee, , Austin" with a stray comma.
Set ignore_empty to FALSE only when position matters, e.g. building fixed-width or CSV output where blanks must be preserved.
Cells containing a formula that returns "" count as empty for ignore_empty = TRUE.
4Numbers, Dates, and Formatting Traps
All three functions convert numbers to plain text, which strips formatting: 0.5 formatted as 50% joins as "0.5", and dates join as serial numbers like "45123". Wrap values in TEXT() to control the output format before joining. This trap is identical in Excel and Google Sheets.
Example
=TEXTJOIN(" ", TRUE, "Due:", TEXT(B2, "mmm d, yyyy"), TEXT(C2, "$#,##0.00"))TEXT(B2, "mmm d, yyyy") converts the date serial to readable text and TEXT(C2, "$#,##0.00") formats the amount — without TEXT you would get "Due: 46096 1250".
Google Sheets uses the same TEXT() format codes for dates and currency, so these formulas port over unchanged.
5Which One Should You Use?
Use TEXTJOIN whenever a delimiter or a range is involved — it is the most capable and the most readable. Use & or CONCAT for quick two-or-three-piece joins where no separator logic is needed. Reach for CONCATENATE only when a workbook must open in Excel 2016 or earlier, where TEXTJOIN and CONCAT do not exist. Microsoft keeps CONCATENATE only for backward compatibility and recommends CONCAT going forward.
Example
=TEXTJOIN("-", TRUE, TEXT(TODAY(), "yyyymmdd"), A2, B2)A real-world ID builder: TEXTJOIN handles the hyphens once, TEXT normalizes the date, and adding more parts later means just appending arguments.
If you're on Excel 2016 or older and need TEXTJOIN behavior, the classic workaround is a helper column chain or upgrading — there is no native substitute.
Functions Used
TEXTJOIN
TEXTJOIN joins text from multiple ranges or strings using a delimiter you specify, with an option to ignore empty cells.
CONCAT
CONCAT joins two or more text strings into one string.
CONCATENATE
CONCATENATE joins two or more text items into one text string. It is the legacy join function; CONCAT and TEXTJOIN are newer and more flexible.
Related Guides
Master Excel Dynamic Arrays (Excel 365)
Learn how to use modern dynamic array functions like FILTER, SORT, UNIQUE, and SEQUENCE to transform your spreadsheets.
VLOOKUP with Multiple Criteria
VLOOKUP only matches one column, but real lookups often need two or more. Learn four reliable ways to do a VLOOKUP with multiple criteria in Excel and Google Sheets.
Calculate Age From Birthday
Learn how to calculate age from birthday in Excel and Google Sheets using DATEDIF, YEAR and TODAY — get exact years, or years, months and days, that update automatically.
Summary
CONCATENATE, CONCAT, and TEXTJOIN all merge text, but they are not equals. CONCATENATE is the legacy option kept for old workbooks, CONCAT modernizes it with range support, and TEXTJOIN adds the two features you actually need daily: a delimiter argument and empty-cell skipping. Default to TEXTJOIN for lists, addresses, and IDs; use & for quick two-piece joins; keep CONCATENATE only for pre-2019 compatibility.
Next Steps
- Rebuild one of your manual CONCATENATE formulas with TEXTJOIN and a range
- Combine TEXTJOIN with TEXT() to build clean date-stamped IDs
- Explore dynamic arrays — TEXTJOIN pairs perfectly with FILTER to join only matching rows