The Excel CONCATENATE function, explained interactively.
CONCATENATE joins up to 255 text values head-to-tail. It’s the legacy version — Excel 2019 renamed it to CONCAT and added TEXTJOIN (which accepts a delimiter argument, fixing the biggest pain of CONCATENATE). Still ubiquitous in legacy spreadsheets, still a top search query, still worth understanding before you reach for the modern alternatives.
How to use CONCATENATE
- Type
=CONCATENATE(and supply the first value — a cell reference, literal string, or formula returning text. - Add a comma and the next argument. If you want a space or other separator between values, pass it as a literal:
" ",", ", etc. - Keep going — up to 255 arguments total. Close the parenthesis when done. Output is a single joined string.
- Prefer
CONCAT(Excel 2019+) for the same behaviour with a shorter name. TEXTJOIN when a delimiter or skip-blanks is involved. The&operator for simple 2-3 value joins.
Click the separator token in the formula bar to swap between space, comma-space, empty, and dash. The “Last, First” preset shows what CONCATENATE has no shortcut for — you literally reverse the cell arguments; there’s no reorder option.
& operator does the same thing in fewer keystrokes.| A | B | C | |
|---|---|---|---|
| 1 | First | Last | Joined |
| 2 | Ava | Patel | Ava Patel |
| 3 | Ben | Carson | Ben Carson |
| 4 | Chloe | Kim | Chloe Kim |
| 5 | Dev | Ramirez | Dev Ramirez |
| 6 | Eva | Nakamura | Eva Nakamura |
=CONCATENATE(A2, "", B2). separator = " ": the canonical full-name join. =CONCATENATE(A2, " ", B2) stitches First and Last with a single space.CONCATENATE syntax and arguments
Variadic — up to 255 text arguments. See Microsoft’s official CONCATENATE reference for the canonical specification.
TEXT() if you need specific formatting (dates, currency, thousand separators). No separator or delimiter parameter exists; literals like " " or ", " are written as their own positional arguments.CONCATENATE examples
Four patterns that cover most real-world CONCATENATE use.
Example 1: CONCATENATE — build a full name
The canonical case. First name + space + last name in a single cell for mail-merge or display purposes.
Returns "Ava Patel" from cells A2 = "Ava", B2 = "Patel". The & equivalent is =A2 & " " & B2 — same result, fewer keystrokes.
Example 2: CONCATENATE with TEXT() for formatted numbers
Numeric arguments get General-formatted before joining. Wrap them with TEXT() to preserve currency, date, or percentage formatting.
Without TEXT, a date cell returns its serial number (45412) instead of the readable date. Works identically for currency ("$#,##0.00") and percentages ("0.0%").
Example 3: CONCATENATE — build a URL
Stitch a base URL with a dynamic path segment (user ID, product slug, sheet name).
Produces clickable URLs when the cell is formatted as a hyperlink. For email links, prefix with "mailto:". The HYPERLINK function wraps this pattern with a displayed text argument: =HYPERLINK(CONCATENATE("…", A2), "View").
Example 4: CONCATENATE — compose an ID from parts
Build a compound identifier by joining region, year, and sequence. Common for SKUs, ticket numbers, audit codes.
Returns "NYC-0042" from A2 = "NYC", B2 = 42. The TEXT(B2, "0000") zero-pads the sequence number; without it, 42 would join as-is giving "NYC-42".
Common CONCATENATE errors and fixes
Four failure modes, each with what to check and how to recover.
CONCATENATE loses a number’s formatting
Cause: numeric arguments are coerced to text via the General format, discarding currency symbols, date separators, or percentage signs.
Wrap numeric arguments with TEXT() and an explicit format string: TEXT(A2, "$#,##0.00") for currency, TEXT(A2, "yyyy-mm-dd") for dates. Not wrapping is the single most common CONCATENATE gotcha.
CONCATENATE returns the range reference as text
Cause: you passed a multi-cell range (A2:A8) as a single argument. CONCATENATE is scalar — it expects one value per argument.
For range-to-string, switch to TEXTJOIN: =TEXTJOIN(", ", TRUE, A2:A8). TEXTJOIN takes a delimiter, an ignore-blanks flag, and handles ranges natively — the right tool for this job.
CONCATENATE result is longer than 32,767 characters
Cause: you hit Excel’s cell length limit. Joining many long values can overflow. The result truncates or errors silently.
Split the output across multiple cells, or filter the source data to only the rows you need. Use LEN() to measure the running length before committing: =LEN(CONCATENATE(…)) tells you if you’re approaching the limit.
CONCATENATE returns #NAME? in Google Sheets
Cause: older Google Sheets versions only supported the & operator. Modern Google Sheets supports both CONCATENATE and CONCAT, but name typos trigger #NAME?.
Check the spelling exactly — CONCATENATE, one word, no space. Copy-paste from a working cell if unsure. The & operator is a safer cross-platform choice: =A2 & " " & B2 works everywhere.
CONCATENATE vs CONCAT, TEXTJOIN & &
Four ways to join text. Pick by how many values, whether you need a delimiter, and which Excel version you support.
| Function / operator | Delimiter arg? | Range support? | Available in |
|---|---|---|---|
| CONCATENATE | No — pass literals | No | Every Excel version (legacy) |
| CONCAT | No — pass literals | Yes | Excel 2019 / 365, Sheets |
| TEXTJOIN | Yes — first arg | Yes + ignore_empty | Excel 2019 / 365, Sheets |
& operator | No — insert inline | No | Every Excel version |
Rule of thumb: & for simple 2-3 value joins (shortest, reads best). TEXTJOIN when you have a range and want a delimiter. CONCAT for 4+ scalar values in modern Excel. CONCATENATE only when maintaining legacy formulas that already use it or when you need to support Excel 2016 and older exclusively.
CONCATENATE frequently asked questions
6.01Is CONCATENATE deprecated in Excel?▸
Not removed, but superseded. Excel 2019 renamed it to CONCAT (same behaviour, shorter name) and added TEXTJOIN (accepts a delimiter argument). CONCATENATE still works in every version for backwards compatibility; new formulas should prefer CONCAT for simple joins and TEXTJOIN when a delimiter is involved.
6.02What’s the difference between CONCATENATE and the & operator?▸
They’re functionally identical. =CONCATENATE(A2, " ", B2) and =A2 & " " & B2 return the same text. The & operator is faster to type and reads more naturally for simple joins. Reach for CONCATENATE / CONCAT when you have many arguments and want a more structured formula.
6.03How do I add a space between concatenated cells?▸
Pass the space as its own argument: =CONCATENATE(A2, " ", B2). CONCATENATE has no separator parameter — if you want a space, comma, or any literal between values, you write it as an explicit argument. The TEXTJOIN function fixed this by accepting a delimiter that it inserts automatically.
6.04Why does CONCATENATE mess up my date or currency format?▸
Because CONCATENATE coerces numbers to text using the General format, not the cell’s displayed format. =CONCATENATE("Due: ", A2) on a date returns something like "Due: 45412" (the serial number). Wrap numeric arguments with TEXT() to lock the format: =CONCATENATE("Due: ", TEXT(A2, "yyyy-mm-dd")).
6.05Can CONCATENATE join a whole range in one go?▸
No — CONCATENATE takes scalar arguments, so =CONCATENATE(A2:A8) returns an error or only the first cell depending on version. To join a range, use TEXTJOIN(delimiter, ignore_empty, range). TEXTJOIN is the dedicated tool for range-to-single-string operations and should be your default for that use case.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.