CONCATENATE vs CONCAT vs TEXTJOIN: which to use?
Three generations of the same idea: CONCATENATE is the legacy function, CONCAT adds range support, and TEXTJOIN adds the two things everyone actually wants — a delimiter and blank-skipping. (And the & operator quietly beats all three for two or three values.)
The differences at a glance
| CONCATENATE | CONCAT | TEXTJOIN | |
|---|---|---|---|
| Accepts whole ranges (A2:A10) | No — cell by cell | Excel & LibreOffice yes — Google Sheets no (#N/A in our executed run) | Yes |
| Delimiter between values | Manual — you type every separator | No | Yes — first argument |
| Skip empty cells | No | No | Yes — second argument TRUE |
| Availability | Everywhere (legacy, kept for compatibility) | Excel 2016+ / Sheets / LibreOffice — but Sheets' CONCAT is a different, two-argument function | Excel 2016+ / Sheets / LibreOffice (verified in every LO version we test) |
Which should you use?
- CONCATENATE — Only when a file must open in genuinely ancient Excel (2013 or older). Otherwise there is no reason to start using it today — & is shorter.
- CONCAT — You want to mash a range together with NO separator, e.g. reassembling split digits — and the file will stay in Excel or LibreOffice. Google Sheets documents CONCAT(value1, value2) as taking exactly two scalar values, and our executed Sheets run returns #N/A for =CONCAT(A1:A3) and for any three-argument call. Use TEXTJOIN there.
- TEXTJOIN — Almost every real join: lists with commas, names with spaces, skipping blanks — =TEXTJOIN(", ",TRUE,A2:A10) is the one to remember.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
We executed CONCATENATE and TEXTJOIN in real LibreOffice across four releases — both work in all of them (TEXTJOIN needs the _xlfn storage prefix in raw .xlsx files, a file-format quirk, not a support gap) — and both also returned their documented values in our dated Google Sheets run. CONCAT is the odd one out: it evaluated correctly in all four LibreOffice builds, but in Google Sheets only the two-argument form worked. =CONCAT("a","b","c"), =CONCAT(A1:A3) and =CONCAT(A1:B2) all returned #N/A there, because Google documents CONCAT as taking exactly two scalar values. Full executed tables in the guide linked below.
Example formulas
| The & operator (best for a few values) | =A2&" "&B2 |
| TEXTJOIN (delimiter + skip blanks) | =TEXTJOIN(", ",TRUE,A2:A10) |
Full per-version details on each function page: CONCATENATE · CONCAT · TEXTJOIN.
See also: CONCAT takes exactly two arguments in Google Sheets.