🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel CONCAT Function — The Successor to CONCATENATE That Eats Whole Ranges

|

Excel CONCAT Function — The Successor to CONCATENATE That Eats Whole Ranges

TL;DR — CONCAT is the modern replacement for CONCATENATE. The one thing it does that its predecessor can't: take an entire range at once, =CONCAT(A1:A10), instead of listing cells A1, A2, A3, … by hand. What it does not do: insert a delimiter or skip blanks — it just runs the text together. Syntax: =CONCAT(text1, [text2], …). If you need a separator or want empty cells skipped, don't patch CONCAT with & — jump straight to TEXTJOIN. Excel 365 and 2019+.

=CONCAT(A1:A10)
=CONCAT(A2, " — ", B2)

CONCATENATE has been in Excel for decades, and it still works — but Microsoft's own docs call it a function "kept for backward compatibility," replaced by CONCAT. The difference that matters in daily use is small but real: CONCAT can swallow a whole range in one reference, where CONCATENATE makes you point at every cell individually.

What you'll learn

  • The one real upgrade over CONCATENATE: ranges instead of cell-by-cell
  • Why CONCATENATE is a legacy leftover you can stop writing
  • How CONCAT reads a 2-D range — row by row, which surprises people
  • The number-format trap (dates become serial numbers, currency loses its symbol)
  • When to skip CONCAT entirely and use TEXTJOIN

The mental model: CONCATENATE that finally takes a range

CONCATENATE was always a little absurd: to join ten cells you typed =CONCATENATE(A1, A2, A3, A4, A5, A6, A7, A8, A9, A10). CONCAT fixes exactly that one thing — it accepts a range, so the same join is =CONCAT(A1:A10). Everything else is identical: no separator goes between the values, and blank cells contribute nothing visible but aren't skipped in any meaningful sense (there's just nothing to skip — empties add no characters):

' Old — every cell listed by hand:
=CONCATENATE(A1, A2, A3, A4, A5)

' CONCAT — one range:
=CONCAT(A1:A5)

The rule that unlocks everything: no delimiter means you'll usually want TEXTJOIN

This is the judgment most people get wrong. CONCAT runs text together with nothing between values. The instant you need a comma, a space, or a line break between them — or you want to drop blanks — you're past what CONCAT is for. People try to bolt a separator on:

=CONCAT(A2, ", ", B2, ", ", C2)   ' works for a few cells, but you're typing
                                  ' the separator by hand again — the old pain

…which is CONCATENATE with a new name. If the answer involves a delimiter, TEXTJOIN does it in one argument (=TEXTJOIN(", ", TRUE, A2:C2)) and skips blanks too. Reserve CONCAT for the genuine "smash these together with no separator" case — building an ID from parts, stitching a code, gluing a prefix to a value.

How CONCAT reads a 2-D range: row by row

When you hand CONCAT a rectangular range, the order it reads in surprises people: it goes row by row (left to right, top to bottom), not column by column:

' A1=a  B1=b
' A2=c  B2=d
=CONCAT(A1:B2)   ' -> "abcd"   (a, b, then c, d — across each row first)

If you expected "acbd", that's the gotcha. For anything where order matters, spell out the sequence explicitly rather than trusting a block's reading order.

The number-format trap

CONCAT joins the underlying value, not what you see on screen. A date shows as its serial number, currency loses its $ and decimals, a percentage becomes a raw fraction:

=CONCAT("Due: ", B2)            ' -> "Due: 46204"   if B2 is a date
=CONCAT("Due: ", TEXT(B2, "yyyy-mm-dd"))   ' -> "Due: 2026-06-17"   (wrap in TEXT)

Whenever a number, date, or currency value goes into CONCAT (or TEXTJOIN, or &), wrap it in TEXT() with an explicit format. This is the most common reason joined output "looks wrong." Note the same 32,767-character per-cell ceiling applies — overflow returns #VALUE!.

The judgment call: CONCAT, CONCATENATE, or TEXTJOIN?

Stop writing CONCATENATE in new formulas — CONCAT does everything it does and takes ranges. Between the two survivors: if you want no separator, use CONCAT; the moment you want a delimiter or blank-skipping, use TEXTJOIN. That's the entire decision. The failure mode to avoid is reaching for CONCAT and then propping it up with & and SUBSTITUTE to fake what TEXTJOIN gives you for free.

How ExcelMaster helps

Most "join these" requests carry hidden formatting rules: build the SKU from category, a dash, and a zero-padded number. ExcelMaster picks the right function — CONCAT for raw glue, TEXTJOIN when a separator or blank-skipping is involved — wraps numbers and dates in the correct TEXT() format, and writes the formula from your description. You say what the output should read like; it chooses the mechanics.

Frequently asked questions

What's the difference between CONCAT and CONCATENATE?

CONCAT can take a whole range (=CONCAT(A1:A10)); CONCATENATE makes you list each cell by hand. Otherwise they behave the same — no delimiter, no blank-skipping. CONCATENATE is kept only for backward compatibility, so use CONCAT in new work.

How do I concatenate a range of cells in Excel?

Use =CONCAT(A1:A10). If you need a separator between the values or want empty cells skipped, use =TEXTJOIN(", ", TRUE, A1:A10) instead — CONCAT has no delimiter option.

How do I add a separator with CONCAT?

You can't, cleanly — CONCAT runs values together with nothing between them. Typing the separator by hand (=CONCAT(A2, ", ", B2)) works for a couple of cells but defeats the purpose. For a delimited list, use TEXTJOIN.

Why does CONCAT show a date as a number?

CONCAT joins the underlying value, not the displayed format, so a date appears as its serial number. Wrap it in TEXT: =CONCAT("Due: ", TEXT(B2, "yyyy-mm-dd")).

Does CONCAT work in Excel 2016?

No. CONCAT arrived in Excel 2019 and 365. On 2016 you fall back to CONCATENATE or the & operator.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-06-17.

Related guides: Excel TEXTJOIN · Excel TEXTSPLIT · Excel FILTER · VBA Concatenate