CONCATENATE Function
CONCATENATE still calculates everywhere and is still the wrong thing to type into a new formula. Microsoft keeps it so that old workbooks keep opening: CONCAT replaced it and accepts whole ranges, the ampersand is shorter than either, and TEXTJOIN is the only one of the four that takes its separator once and skips blank cells for you. So the real question on this hub is which of those four a given job wants — and, more often than that, why the join produced 45892 where a date was expected, which is a formatting problem rather than a joining one.
The formula
=CONCATENATE(A2, " ", B2) join two cells with a space (legacy, kept for compatibility)
=A2&" "&B2 identical result, shorter to type
=CONCAT(A2, " ", B2) identical result in Excel; in Sheets, CONCAT only takes exactly two arguments
=TEXTJOIN(", ", TRUE, A2:A10) join a whole range with a delimiter, skipping blank cellsA worked example
First name in A2 ("Priya"), last name in B2 ("Nair").
=CONCATENATE(A2, " ", B2)
"Priya Nair" — one text string. =A2&" "&B2 and =CONCAT(A2," ",B2) return the identical result; which one to use is about matching what the rest of the workbook already does, not correctness.
Which one do I need?
| If you want to… | Use |
|---|---|
| You're editing an older workbook that already uses CONCATENATE | Keep using CONCATENATE(text1, text2, ...) — it still works, just don't start new formulas with it |
| Writing a new formula for a handful of cells | The & operator: =A2&" "&B2 — shortest to type and works identically in Excel and Google Sheets |
| You need CONCAT specifically for a whole range | =CONCAT(A2:A10) works in Excel; in Google Sheets, CONCAT only accepts exactly two values, so use TEXTJOIN or & for a range instead |
| Joining a whole range or column, especially with blanks in it | =TEXTJOIN(delimiter, TRUE, range) — the only one of the four that skips blank cells for you |
| Combining text with a date or number so it reads correctly | Wrap it first: ="Order date: "&TEXT(A2,"mm/dd/yyyy") — joining a raw date cell shows its serial number, not the date |
| Inserting a line break between the joined values | =A2&CHAR(10)&B2, with Wrap Text turned on so the break actually displays |
Frequently asked questions
What's the difference between CONCATENATE and CONCAT?
They return the same result for a handful of arguments, but CONCAT also accepts whole ranges as a single argument (=CONCAT(A2:A10)), where CONCATENATE cannot join a range — only individual text arguments. Microsoft lists CONCATENATE as a legacy function kept for backward compatibility, so CONCAT is the recommended replacement.
Does Google Sheets' CONCAT work exactly like Excel's?
No — Google Sheets' CONCAT only accepts exactly two arguments (CONCAT(value1, value2)), not the multiple arguments or ranges Excel's CONCAT allows. In Sheets, use CONCATENATE, the & operator, or TEXTJOIN when you need to join more than two values.
Why does concatenating a date show a number like 45892 instead of a date?
A date is stored as a serial number, and & or CONCATENATE join that raw stored value, not its displayed format. Wrap the date in TEXT first: ="Due: "&TEXT(A2,"mm/dd/yyyy").
How do I remove a CONCATENATE formula but keep the joined text?
Select the cells, copy them (Ctrl+C), then Paste Special > Values (Ctrl+Alt+V, then V) over the same cells. This replaces the live formula with its static text result.
New guides and tools, once a month
DE + EN · double opt-in · no spam