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 | Yes | 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 | 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.
- TEXTJOIN — Almost every real join: lists with commas, names with spaces, skipping blanks — =TEXTJOIN(", ",TRUE,A2:A10) is the one to remember.
Compatibility (from executed tests)
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). In Google Sheets all three are available.
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.