Function comparison · engine-measured

CONCATENATE vs TEXTJOIN

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.

fx
CONCATENATE(…)  vs  TEXTJOIN(…)
7
measured scenarios
252
vs 126 arguments
1
delimiter argument
2019+
TEXTJOIN availability
▶ Try both live See the feature matrix
🏆Verdict: TEXTJOIN for lists and ranges; CONCATENATE only for legacy two-cell joins.

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.

CONCATENATE strengths

  • Available in every Excel version
  • Simple for joining two or three fixed cells
  • The & operator is a quick shorthand
  • Predictable: keeps empty cells as empty text

TEXTJOIN strengths

  • One delimiter for the whole join
  • A whole range in a single argument
  • ignore_empty drops blanks and stray separators
  • 252 arguments vs CONCATENATE's 126

Feature-by-feature matrix

Every ✓ / ◐ / ✗ is a behavior we evaluated in HyperFormula 3.5 — not a marketing claim.

CapabilityCONCATENATETEXTJOINWhy it matters
Delimiter argument✗ No — repeat it manually✓ Yes — one argumentTEXTJOIN inserts it between every item.
Whole-range argument✗ Excel #VALUE! / HF joins raw✓ Yes — A2:A100TEXTJOIN takes ranges natively.
Skip empty cells✗ No — gaps remain✓ Yes — ignore_empty TRUEMeasured: '||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+ / 365CONCATENATE is the legacy-safe choice.
Speed (HyperFormula)✓ ~8–12ms / 200 joins✓ ~5–9ms / 200 joinsBoth negligible — choose on features.

Syntax, side by side

CONCATENATE interleaves separators by hand; TEXTJOIN takes one delimiter and an ignore_empty flag.

CONCATENATE

=CONCATENATE(text1, [text2], …)
text1, text2 …Up to 255 items, each a cell, a literal, or a number. No delimiter argument — you insert separators as their own items.
Empty cellsIncluded as empty strings, so separators around them leave visible gaps.
RangesDesktop Excel rejects a range argument (#VALUE!); HyperFormula joins it raw with no delimiter.
=CONCATENATE(A2," ",B2)

TEXTJOIN

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
delimiterThe separator inserted BETWEEN items once, e.g. ", " or " ". Can be empty.
ignore_emptyTRUE skips blank cells (and their delimiters); FALSE keeps the gaps.
text1 …Cells, literals, or whole RANGES — and array expressions like IF(...) for conditional joins.
=TEXTJOIN(", ",TRUE,A2:C2)

Live head-to-head playground

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.

A1Sample data — click any cell to edit; both formulas re-evaluate live

CONCATENATE

—

TEXTJOIN

—
HyperFormula 3.5 · live

Every difference, measured

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.

Join two cells with a space — the tie

Build Ada's full name from First (A2) and Last (B2).
CONCATENATE
=CONCATENATE(A2," ",B2)
Ada Lovelace
TEXTJOIN
=TEXTJOIN(" ",TRUE,A2,B2)
Ada Lovelace
Both return 'Ada Lovelace'. For two fixed cells with a space, CONCATENATE, TEXTJOIN and the & operator are interchangeable.

A whole range — CONCATENATE's hard wall

Join every First name in A2:A7 into one comma-separated list.
CONCATENATE
=CONCATENATE(A2:A7)
AdaAlanGraceLinusMargaret
TEXTJOIN
=TEXTJOIN(", ",TRUE,A2:A7)
Ada, Alan, Grace, Linus, Margaret
In HyperFormula, CONCATENATE(A2:A7) joins the whole range raw → 'AdaAlanGraceLinusMargaret' with NO delimiter; TEXTJOIN adds ', ' between each. Note the engine difference: desktop Excel rejects a range argument to CONCATENATE with #VALUE! — either way it cannot produce a delimited list, which is TEXTJOIN's job.

One delimiter, many cells

Join First, Last and City with ', ' between each.
CONCATENATE
=CONCATENATE(A2,", ",B2,", ",C2)
Ada, Lovelace, London
TEXTJOIN
=TEXTJOIN(", ",TRUE,A2:C2)
Ada, Lovelace, London
Both return 'Ada, Lovelace, London'. CONCATENATE repeats ', ' as a separate item between every pair; TEXTJOIN states it once. With ten cells the manual version is nine extra arguments.

Empty cells — ignore_empty removes the gaps

Row 5 has blank First and Last, City = Boston. Join with '|' so the gaps are visible.
CONCATENATE
=CONCATENATE(A5,"|",B5,"|",C5)
||Boston
TEXTJOIN
=TEXTJOIN("|",TRUE,A5:C5)
Boston
The measured gap: row 5 has blank First and Last. CONCATENATE keeps the separators around the blanks → '||Boston'. TEXTJOIN with ignore_empty TRUE removes the blanks AND their delimiters → 'Boston'. This is the single biggest reason to switch.

ignore_empty FALSE — TEXTJOIN keeps the gaps too

Same blank row, but TEXTJOIN is told NOT to skip empties, with '|' as delimiter.
CONCATENATE
=CONCATENATE(A5,"|",B5,"|",C5)
||Boston
TEXTJOIN
=TEXTJOIN("|",FALSE,A5:C5)
||Boston
Both return '||Boston'. With ignore_empty FALSE, TEXTJOIN behaves like CONCATENATE and preserves the gaps — useful when the empty positions are meaningful (e.g. fixed-width CSV columns).

Conditional join — only the London rows

Join the First names of people whose City is London.
CONCATENATE
=CONCATENATE(IF(C2="London",A2,""),IF(C3="London",A3,""))
AdaAlan
TEXTJOIN
=TEXTJOIN(", ",TRUE,IF(C2:C7="London",A2:A7,""))
Ada, Alan
CONCATENATE needs a manual IF per cell and still has no delimiter → 'AdaAlan'. TEXTJOIN wraps one array IF(C2:C7="London",A2:A7,"") and joins only the matches with ', ' → 'Ada, Alan'. Conditional joins are where TEXTJOIN pulls decisively ahead.

The & operator — CONCATENATE's shorthand

The same full name built with the & operator instead of the function.
CONCATENATE
=A2&" "&B2
Ada Lovelace
TEXTJOIN
=TEXTJOIN(" ",TRUE,A2,B2)
Ada Lovelace
Both return 'Ada Lovelace'. The & operator is the shorthand form of CONCATENATE — same result, fewer characters, and it works in every Excel version.

Engine benchmark

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 sizeCONCATENATETEXTJOIN
1,000 rows × 200 lookups12.05 ms9.42 ms
5,000 rows × 200 lookups8.59 ms6.75 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

Fixed two-cell join in any Excel → CONCATENATE; lists, ranges, or blank-skipping → TEXTJOIN.

SituationUseWhy
Join a whole row or column into a listTEXTJOINTEXTJOIN takes a range in one argument; CONCATENATE must list every cell.
Some cells may be blankTEXTJOINignore_empty TRUE drops blanks and their separators; CONCATENATE leaves stray separators.
One delimiter between every itemTEXTJOINTEXTJOIN applies the delimiter once; CONCATENATE interleaves it by hand.
A fixed two- or three-cell joinEitherBoth are fine for a small fixed join — CONCATENATE or the & operator is slightly shorter.
The workbook must open in pre-2019 ExcelCONCATENATETEXTJOIN arrived in Excel 2019 / 365; CONCATENATE works everywhere.
More than 126 pieces to joinTEXTJOINTEXTJOIN allows 252 arguments; CONCATENATE caps at 126.
You want blanks preserved as empty textCONCATENATECONCATENATE keeps empty cells; TEXTJOIN's TRUE would remove them.
A conditional join (only matching rows)TEXTJOINTEXTJOIN(...,IF(range=...)) joins an array in one call; CONCATENATE needs manual IF per cell.

CONCATENATE → TEXTJOIN migration

Replace interleaved separators with one delimiter and per-cell references with a range.

TaskCONCATENATE / old wayTEXTJOINWhat 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).

Frequently asked questions

What is the difference between CONCATENATE and TEXTJOIN?
CONCATENATE simply glues items together — you insert every delimiter as its own argument and empty cells leave gaps. TEXTJOIN adds three things: a single delimiter argument applied between all items, native support for whole ranges, and an ignore_empty flag that skips blank cells. For anything beyond two or three fixed cells, TEXTJOIN is cleaner.
CONCAT vs CONCATENATE vs TEXTJOIN — which should I use?
CONCATENATE is the legacy function (all Excel versions). CONCAT is its 2019+ replacement that accepts ranges but still has no delimiter or ignore_empty. TEXTJOIN (also 2019+) is the full-featured one: delimiter + ranges + ignore_empty. Use TEXTJOIN for delimited joins, CONCAT for a simple range glue, and CONCATENATE or & only when the file must open in old Excel.
What does ignore_empty do in TEXTJOIN?
TRUE skips empty cells AND the delimiters that would surround them, so joining a row with blank First/Last gives 'Boston' not '||Boston'. FALSE keeps the empty positions and their delimiters, giving '||Boston' — useful when each position must stay aligned, like a fixed-column CSV export.
Can CONCATENATE take a range like A2:A100?
Desktop Excel cannot — it returns #VALUE! because CONCATENATE expects individual items. HyperFormula is more permissive and joins the range raw with no delimiter (measured: 'AdaAlanGraceLinusMargaret'). Neither gives you a delimited list; that is exactly what TEXTJOIN(", ",TRUE,A2:A100) is for. CONCAT (2019+) also accepts ranges but without a delimiter.
Which Excel versions support TEXTJOIN?
TEXTJOIN and CONCAT arrived in Excel 2019 and Microsoft 365, and both work in Google Sheets. They are absent from Excel 2016 and earlier. CONCATENATE and the & operator work in every version, which is why they remain the fallback for widely-shared legacy files.