CONCAT Function in Excel — Complete Guide (2026)

CONCAT Function in Excel — Complete Guide (2026)

The CONCAT function in Excel does one job and does it cleanly. It joins text. You hand it a list of strings, cell references, or whole ranges, and it stitches them together end-to-end into a single string. Three arguments or three hundred — same syntax, same behavior. =CONCAT(A1, " ", B1) turns "John" and "Smith" into "John Smith" in about as many keystrokes as it takes to type the function name.

Here's what makes CONCAT different from the older CONCATENATE function that you've probably seen in tutorials from 2015. CONCAT accepts ranges. You can hand it =CONCAT(A1:A10) and it'll glue every value in that 10-cell range into one long string. CONCATENATE refused — you had to comma-separate every single cell. That's the whole reason Microsoft added CONCAT in Excel 2019 and rolled it into 365. The old function still works for backward compatibility, but new spreadsheets should use the newer one. Less typing, fewer errors, easier to maintain when ranges grow.

This guide walks the function from the syntax up to the edge cases. Real examples for combining names, building order numbers, joining a range, adding spaces and separators, concatenating with formatted dates and numbers. Plus the three competing approaches — CONCAT, TEXTJOIN, and the ampersand operator — and when each one wins.

You'll also see common errors (the dreaded #VALUE! with arrays, missing separator gotchas), Power Query's Merge Columns alternative, TEXTSPLIT for going the other way, and Mac Excel parity notes. Worth knowing the full toolkit because Excel handles text joining in at least four different ways, and picking the wrong one slows you down.

What CONCAT Actually Does

CONCAT is short for concatenate, which is Latin-derived jargon for "chain together." In plain English: it sticks strings together. "Hello" + " " + "World" becomes "Hello World". That's all it does. No formatting tricks, no calculations, no delimiters added automatically — you supply every character that ends up in the result, including any spaces or commas between values.

This means CONCAT is a building block, not a finished tool. You combine it with other functions to do real work. Pair it with TEXT() to format dates and numbers before they get joined. Pair it with IF() to conditionally include some pieces and skip others. Pair it with RIGHT(), LEFT(), and MID() to extract substrings before joining. Worth thinking of CONCAT less as a feature and more as glue between other features.

CONCAT vs CONCATENATE — Why CONCAT Won

CONCATENATE shipped in every version of Excel from 95 onward. It did the job for two decades. Then in Excel 2019, Microsoft added CONCAT and quietly marked CONCATENATE as deprecated. Old formulas still work — they don't break — but new spreadsheets should use CONCAT.

The reason is one feature: ranges. CONCATENATE took a comma-separated list of values. =CONCATENATE(A1, B1, C1, D1, E1) joined five cells. To add five more you typed five more arguments. Joining A1:A50 meant typing 50 arguments by hand. Brutal. CONCAT cut that to =CONCAT(A1:A50) — one range reference, done.

The second reason was internal cleanup. CONCATENATE's argument handling didn't play well with newer dynamic-array functions like FILTER and UNIQUE. CONCAT was rewritten to integrate cleanly with those — it spills naturally when arguments are dynamic arrays, and it composes cleanly with the new function family.

For backward compatibility, CONCATENATE still works in every modern Excel. If you inherit a workbook written in 2018 with CONCATENATE everywhere, leave it alone — there's no benefit to refactoring working formulas. But for anything new, default to CONCAT. The syntax is identical for single-cell arguments. The range support is a free upgrade.

The Ampersand Operator — Still Going Strong

Long before CONCATENATE existed, Excel had the ampersand (&) operator. =A1 & " " & B1 does exactly the same thing as =CONCAT(A1, " ", B1). Three characters shorter, easier to read for short joins, and works in every version of Excel ever shipped. Many veteran users prefer it for two-or-three-cell joins because the prose-like syntax ("this and that and the other") matches how you'd describe the operation out loud.

Where ampersand falls down is ranges. =A1:A10 & "" doesn't join the range — it spills into a vertical array of the original values. To join a range you have to fall back to CONCAT or TEXTJOIN. So the rule of thumb most experienced users settle on: ampersand for 2-3 cells, CONCAT for ranges, TEXTJOIN for delimited ranges.

TEXTJOIN — When You Need a Delimiter on Every Item

TEXTJOIN solves the one thing CONCAT can't. CONCAT joins a range with no separator. TEXTJOIN joins a range and inserts a separator between every item automatically. =TEXTJOIN(", ", TRUE, A1:A5) joins five cells with a comma-space between each, and the TRUE argument tells Excel to skip blank cells (otherwise you get "Apple, , Cherry" with an empty gap).

TEXTJOIN Syntax

Three arguments. The first is the delimiter — any string. The second is ignore_empty — TRUE or FALSE. The third (and beyond) is the values to join. =TEXTJOIN(" | ", TRUE, A1:A10) joins ten cells with pipe separators. =TEXTJOIN(CHAR(10), TRUE, A1:A10) joins them with line breaks (useful for building multi-line cell content). CHAR(10) is the line-feed character — combine with cell wrap text formatting to see the breaks.

TEXTJOIN's ignore_empty flag is the killer feature. Real spreadsheets have gaps. Some rows have all five values, some have only three. TEXTJOIN with TRUE handles this gracefully — empty cells just get skipped, no double-separator artifacts. CONCAT can't do this without an IF wrapper around every argument.

Like CONCAT, TEXTJOIN is Excel 2019+ and 365. It does not exist in 2016 or earlier. If you're stuck on an old version, you're back to ampersand operators and a lot of typing. Worth knowing that Excel cheat sheets often list TEXTJOIN as one of the top three modern functions to memorize, alongside FILTER and XLOOKUP.

The Ampersand (&) Operator — Still the Fastest for Short Joins

The ampersand has been Excel's default string-join operator forever. =A1 & B1 joins A1 and B1. =A1 & " " & B1 adds a space between them. Same result as CONCAT, less typing, more readable for short cases.

It's also more forgiving of mixed types. =A1 & B1 where A1 is a number and B1 is text just works — Excel coerces the number to its display string and joins. CONCAT does the same, but the ampersand syntax is so compact that most spreadsheet veterans use it as the default for two-cell joins and reach for CONCAT only when ranges are involved.

One thing to watch: when joining numbers, the ampersand uses the underlying value, not the displayed format. If A1 shows "$1,234.56" but is actually stored as 1234.56, =A1 & " total" gives "1234.56 total" — no dollar sign, no commas. Wrap with TEXT to preserve formatting: =TEXT(A1, "$#,##0.00") & " total".

When to Use CONCAT vs TEXTJOIN vs Ampersand

Use Ampersand (&) When:

You're joining 2 or 3 specific cells. The formula stays short and readable. Most beginners learn ampersand first and stick with it forever for simple cases. There's nothing wrong with that — for two-cell joins, =A1 & " " & B1 is clearer than =CONCAT(A1, " ", B1). Less syntactic noise, same result.

Use CONCAT When:

You have a range of cells to join with no separator (or you'll add the separator manually between each piece). =CONCAT(A1:A10) is dramatically cleaner than 10 ampersand operators. Also use CONCAT when you're building structured output by mixing literals, single cells, and ranges in one formula.

Use TEXTJOIN When:

You have a range and want the same delimiter between every item. Comma-separated lists, pipe-separated values, line-break-joined text. Plus you want to skip blanks automatically. TEXTJOIN is the only one of the three that handles this without extra IF logic.

Concatenating With Number and Date Formatting

CONCAT joins raw values. If a cell holds a date stored as the serial number 45798 but displays as "2025-05-21", CONCAT spits out "45798" — not the formatted date. To preserve formatting you wrap the cell in TEXT() inside the CONCAT call. =CONCAT(A1, " ", TEXT(B1, "yyyy-mm-dd")) joins A1 and B1 where B1 is a date, formatted as "2025-05-21". The TEXT function converts the underlying value to a formatted string before CONCAT sees it.

Same trick for numbers with formatting. =CONCAT("Price: ", TEXT(B1, "$#,##0.00")) turns the raw number 1234.56 into "Price: $1,234.56". Without TEXT, you'd get "Price: 1234.56" — accurate but ugly.

Common TEXT format codes worth knowing: "yyyy-mm-dd" for ISO dates, "mmm dd, yyyy" for "May 21, 2026", "$#,##0.00" for currency with two decimals, "0.0%" for percentages with one decimal, "00000" for zero-padded IDs like "00425". The format string follows the same rules as Excel's Format Cells dialog, just in formula form.

Common CONCAT Errors and Fixes

#VALUE! When Passing an Array

If CONCAT gets a value it can't coerce to text — typically the result of a function that errored — it returns #VALUE!. Wrap suspect arguments in IFERROR. =CONCAT(IFERROR(A1, ""), " ", B1) swaps any error in A1 for an empty string before joining.

Another #VALUE! cause: passing a 2D array literal directly. =CONCAT({1,2;3,4}) works in 365 but can fail in some 2019 builds. Workaround: put the array in a cell range and reference it instead.

Missing Space Between Values

Beginner classic. =CONCAT(A1, B1) with A1="John" and B1="Smith" returns "JohnSmith". CONCAT doesn't add separators — you have to. Fix: =CONCAT(A1, " ", B1). The space inside the quote marks is the entire fix.

Numbers Showing Raw Instead of Formatted

Already covered above. CONCAT uses the underlying value. Wrap with TEXT to preserve formatting. =CONCAT("Total: ", TEXT(A1, "$#,##0.00")).

Result Truncated at 32,767 Characters

That's Excel's per-cell content limit. CONCAT will silently truncate if your joined string exceeds it. For long joins of huge ranges, split the result across multiple cells or use Power Query instead.

#NAME? Means You're On Excel 2016 or Earlier

If CONCAT returns #NAME? immediately, Excel doesn't recognize the function. You're on a pre-2019 build. Use CONCATENATE or the ampersand operator instead. Or upgrade to Excel 2019, 2021, 2024, or Microsoft 365 — CONCAT is unavailable everywhere else.

Power Query Merge Columns — The Bulk Alternative

For large data sets imported from CSVs or databases, Power Query offers a Merge Columns transform that does the same job as CONCAT — without writing formulas in the sheet. Load your data into Power Query (Data tab → Get Data). Select two or more columns. On the Transform tab pick Merge Columns. Choose a separator (space, comma, custom). Name the new merged column. Close and Load.

The advantage: Power Query handles billions of rows efficiently because it processes data outside the cell grid. CONCAT in 100,000 cells slows the workbook. Power Query merge on the same data finishes in seconds and only displays the result.

The disadvantage: Power Query has a learning curve. For one-off joins of 50 rows, CONCAT in the sheet wins on simplicity. For repeating monthly imports of 10,000 rows, Power Query wins because the transform is reusable — refresh and the merge happens again automatically.

TEXTSPLIT — The Reverse Operation

The opposite of CONCAT is TEXTSPLIT, added in Excel 365 in 2022. Where CONCAT joins values into one cell, TEXTSPLIT takes one cell and splits it into many. =TEXTSPLIT("apple,banana,cherry", ",") spills three cells horizontally: apple, banana, cherry. Same syntax as Text-to-Columns but as a live formula.

TEXTSPLIT pairs naturally with CONCAT. You can build complex transformations like "join three columns into a delimited string, then split that string on a different delimiter into more columns" with two formulas. Or use TEXTSPLIT to unpack data that arrived in concatenated form from a legacy system.

The function takes up to four arguments: text, column_delimiter, row_delimiter, ignore_empty. =TEXTSPLIT(A1, ",", ";") splits A1 on commas horizontally and on semicolons vertically — turning "a,b,c;d,e,f" into a 2×3 grid.

Mac Excel Parity

CONCAT works identically on Mac Excel 2019, 2021, 2024, and 365. No syntax differences. The only quirk is keyboard shortcut differences for formula entry — F4 to toggle absolute references becomes Cmd+T on Mac. The function itself, its arguments, and its behavior are identical across platforms.

TEXTJOIN, TEXTSPLIT, and the ampersand operator are also fully cross-platform. Workbooks built with these functions on Windows open and recalculate correctly on Mac, and vice versa. The only place you'll hit incompatibility is opening a Mac-365-built workbook in Excel 2016 — the newer functions won't exist and you'll see #NAME? errors.

CONCAT Inside Excel Tables — The Structured Reference Win

If your data is formatted as an Excel Table (select range, press Ctrl+T), CONCAT plays especially well with structured references. =CONCAT([@FirstName], " ", [@LastName]) reads cleaner than =CONCAT(A2, " ", B2) and self-documents what columns are being joined.

The bigger win is auto-fill. Write the CONCAT formula in any cell of a new table column, press Enter, and Excel auto-fills it down every existing row of the table. Add a new row of data tomorrow, and the formula populates that row automatically. No dragging, no reference updates, no broken formulas when columns get inserted somewhere else. Tables make CONCAT effectively self-maintaining.

Worth pairing this with Excel pivot tables — pivots love structured tables as their source data. Build a CONCAT calculated column in the table (full name, customer address, order summary), then pivot on that joined field for cleaner group-by reports. The joined string becomes a meaningful dimension instead of two raw columns mashed together at pivot time.

CONCAT in Conditional Formatting

You can use CONCAT inside conditional formatting formulas too. "Highlight any row where the full name matches a value in another column" becomes =CONCAT($A2, " ", $B2)=$D$1. The CONCAT builds the joined value on the fly, comparison runs against the lookup cell, and matching rows get the conditional fill. No helper column needed.

Same trick works in data validation. Build dropdown lists where each entry is a CONCAT of two source columns. Combine with INDIRECT for cascading dropdowns where the second list depends on the first. CONCAT is the glue that makes structured data play nicely with Excel's higher-level features.

Performance Notes for Large Sheets

CONCAT itself is fast. Joining 10 cells takes microseconds. Where it slows down is when you wrap it around large ranges and apply it in 50,000+ cells. =CONCAT(A:A) in 50,000 cells means Excel scans a million cells fifty thousand times during a recalc. Switch to explicit ranges like =CONCAT(A2:A10000) to skip the empty parts of the column.

For repeated CONCAT operations across huge data sets, prefer Power Query Merge Columns. Power Query processes data outside the cell grid and is orders of magnitude faster on bulk transformations. CONCAT in the sheet is the right tool for ad-hoc joins. Power Query is the right tool for monthly imports with thousands of rows. Same end result, very different performance profile.