One editable name table, two formulas side by side. CONCATENATE glues arguments in order with no delimiter of its own; TEXTJOIN takes one delimiter, accepts a whole range, and can skip blanks. Every result below is evaluated live in HyperFormula 3.5.
TEXTJOIN takes a single delimiter, accepts a whole range in one argument, and drops blanks when ignore_empty is TRUE — so a missing cell never leaves a stray separator. CONCATENATE interleaves separators by hand, lists every cell, and keeps empty cells as empty text (a range argument even raises #VALUE! in Excel). CONCATENATE's only real edge is availability: it works in every Excel version, while TEXTJOIN needs 2019 / 365.
Every ✓ / ◐ / ✗ is a behavior we evaluated in HyperFormula 3.5 — not a marketing claim.
| Capability | CONCATENATE | TEXTJOIN | Why it matters |
|---|---|---|---|
| Delimiter argument | ✗ No — repeat it manually | ✓ Yes — one argument | TEXTJOIN inserts it between every item. |
| Whole-range argument | ✗ Excel #VALUE! / HF joins raw | ✓ Yes — A2:A100 | TEXTJOIN takes ranges natively. |
| Skip empty cells | ✗ No — gaps remain | ✓ Yes — ignore_empty TRUE | Measured: '||Boston' vs 'Boston'. |
| Two fixed cells | ✓ =CONCATENATE(A2," ",B2) | ✓ =TEXTJOIN(" ",TRUE,A2,B2) | Identical output; & is shorter still. |
| & operator shorthand | ✓ A2&" "&B2 | ✗ — | The operator form of CONCATENATE. |
| Conditional array join | ◐ Manual IF per cell | ✓ TEXTJOIN(...,IF(range=...)) | One array formula filters and joins. |
| Compatibility | ✓ Every Excel version | ◐ Excel 2019+ / 365 | CONCATENATE is the legacy-safe choice. |
| Speed (HyperFormula) | ✓ ~8–12ms / 200 joins | ✓ ~5–9ms / 200 joins | Both negligible — choose on features. |
CONCATENATE interleaves separators by hand; TEXTJOIN takes one delimiter and an ignore_empty flag.
Both formulas join the SAME editable grid (First · Last · City). Row 5 has blank First/Last — the ignore_empty difference shows up there. Edit any cell and watch live.
These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.
=CONCATENATE(A2," ",B2)=TEXTJOIN(" ",TRUE,A2,B2)=CONCATENATE(A2:A7)=TEXTJOIN(", ",TRUE,A2:A7)=CONCATENATE(A2,", ",B2,", ",C2)=TEXTJOIN(", ",TRUE,A2:C2)=CONCATENATE(A5,"|",B5,"|",C5)=TEXTJOIN("|",TRUE,A5:C5)=CONCATENATE(A5,"|",B5,"|",C5)=TEXTJOIN("|",FALSE,A5:C5)=CONCATENATE(IF(C2="London",A2,""),IF(C3="London",A3,""))=TEXTJOIN(", ",TRUE,IF(C2:C7="London",A2:A7,""))=A2&" "&B2=TEXTJOIN(" ",TRUE,A2,B2)200 three-cell joins per engine on tables of 1,000 / 5,000 rows. Both ~5–12ms in HyperFormula 3.5 — essentially tied, so choose on features (delimiter, ranges, ignore_empty), not speed. HyperFormula is NOT Excel — relative signal only.
| Table size | CONCATENATE | TEXTJOIN |
|---|---|---|
| 1,000 rows × 200 lookups | 12.05 ms | 9.42 ms |
| 5,000 rows × 200 lookups | 8.59 ms | 6.75 ms |
Fixed two-cell join in any Excel → CONCATENATE; lists, ranges, or blank-skipping → TEXTJOIN.
| Situation | Use | Why |
|---|---|---|
| Join a whole row or column into a list | TEXTJOIN | TEXTJOIN takes a range in one argument; CONCATENATE must list every cell. |
| Some cells may be blank | TEXTJOIN | ignore_empty TRUE drops blanks and their separators; CONCATENATE leaves stray separators. |
| One delimiter between every item | TEXTJOIN | TEXTJOIN applies the delimiter once; CONCATENATE interleaves it by hand. |
| A fixed two- or three-cell join | Either | Both are fine for a small fixed join — CONCATENATE or the & operator is slightly shorter. |
| The workbook must open in pre-2019 Excel | CONCATENATE | TEXTJOIN arrived in Excel 2019 / 365; CONCATENATE works everywhere. |
| More than 126 pieces to join | TEXTJOIN | TEXTJOIN allows 252 arguments; CONCATENATE caps at 126. |
| You want blanks preserved as empty text | CONCATENATE | CONCATENATE keeps empty cells; TEXTJOIN's TRUE would remove them. |
| A conditional join (only matching rows) | TEXTJOIN | TEXTJOIN(...,IF(range=...)) joins an array in one call; CONCATENATE needs manual IF per cell. |
Replace interleaved separators with one delimiter and per-cell references with a range.
| Task | CONCATENATE / old way | TEXTJOIN | What changed |
|---|---|---|---|
| Join two cells | =CONCATENATE(A2," ",B2) | =TEXTJOIN(" ",TRUE,A2,B2) | Delimiter once instead of interleaved; TRUE skips blanks. |
| Join a row | =CONCATENATE(A2,",",B2,",",C2) | =TEXTJOIN(",",TRUE,A2:C2) | A whole range in one argument — no per-cell wiring. |
| Skip a blank | =CONCATENATE(A5,"|",B5,"|",C5) | =TEXTJOIN("|",TRUE,A5:C5) | With a blank cell, CONCATENATE leaves a stray separator; TEXTJOIN drops it. |
| List a column | =CONCATENATE(A2,",",A3,",",A4) | =TEXTJOIN(",",TRUE,A2:A7) | Six names in one range vs six manual references. |
| Conditional join | =CONCATENATE(IF(C2="London",A2,""),IF(C3="London",A3,"")) | =TEXTJOIN(",",TRUE,IF(C2:C7="London",A2:A7,"")) | TEXTJOIN joins the IF array in one call (dynamic-array Excel). |