How to Combine Cells in Excel: CONCAT, TEXTJOIN, Ampersand, and Why Merge Loses Data

How to Combine Cells in Excel Without Losing Your Data

If you have ever tried to glue first names and last names together in a spreadsheet and ended up with a column full of #NAME? errors, a Merge & Center button that quietly wiped half your data, or some bizarre date that turned into 44927, you are not alone. How to combine cells in Excel sounds like it should be a single button. It isn't. Excel offers at least six different ways to combine cells, and each one solves a slightly different problem.

Here's the short version. To combine the contents of two or more cells into one cell, you use a formula like CONCAT, TEXTJOIN, or the ampersand operator (&). If you need to combine cells with a separator (comma, space, line break), TEXTJOIN is what you want. If you want a one-off result without writing a formula at all, Flash Fill with Ctrl+E reads your pattern and fills the column in a fraction of a second.

For combining columns from different tables or workbooks, Power Query is the heavy artillery. And then there's Merge & Center, the option that sits front and center on the Home ribbon. Use it for headings. Never use it on data. We'll explain why below.

This guide walks through every method, when to pick each one, and the gotchas that quietly corrupt your spreadsheet when you aren't paying attention. By the time you finish, you will know how to combine 2 cells in Excel, how to combine multiple cells in Excel into one cell, and how to combine text in two cells in Excel while keeping the data intact. Excel power users learn this stuff in the first month of any decent training. If you want to practice Excel in a structured way, the hub page lists every calculation and formula tutorial we publish.

One more thing before we start. The screenshots and shortcuts below are written for Excel 365 and Excel 2021. Older versions (2019, 2016, and earlier) lack CONCAT and TEXTJOIN — those releases need the older CONCATENATE function or the ampersand operator. We'll point out which method works in which version as we go. Google Sheets users: most of this translates directly, though Sheets handles a few of these slightly differently.

CONCAT and CONCATENATE: The Formula Approach

Of all the ways to combine cells in Excel, the formula path is what most spreadsheet users learn first. There are actually three very closely related functions sitting on top of each other, and the differences are subtle but important.

CONCATENATE is the original. It was introduced in Excel 2000 and remains in every version since. It takes a list of text values or cell references separated by commas and welds them together. The syntax: =CONCATENATE(A2, B2). Microsoft has officially deprecated it but it still works for backward compatibility, and you'll find it everywhere in legacy spreadsheets.

CONCAT is the modern replacement, available in Excel 2019, Excel 2021, and Excel 365. It does everything CONCATENATE does, but it accepts ranges rather than only individual references. So if you want to glue together every value in A2:A10, you can write =CONCAT(A2:A10) and skip the comma-separated mess. That alone is reason enough to switch — typing =CONCATENATE(A2, A3, A4, A5, A6, A7, A8, A9, A10) by hand is the kind of thing that aged a generation of analysts.

TEXTJOIN is the one you actually want most of the time, and we'll get to it next. But first, a practical example. Say you have first names in column A and last names in column B. To make a full-name column, click into C2 and type:

=CONCAT(A2, " ", B2)

That space inside the quotes is the trick. Without it, John Smith becomes JohnSmith. Drag the formula down or double-click the fill handle to copy it down the column. Job done. The same formula in older Excel: =CONCATENATE(A2, " ", B2). Identical result, slightly more typing.

One thing that bites people: CONCAT preserves the cell's display value, not the underlying number. If A2 shows $1,500.00 but the underlying value is 1500, your concatenated result will be 1500, not $1,500.00. To preserve the formatting, wrap it in TEXT: =CONCAT(A2, " — ", TEXT(B2, "$#,##0.00")). Dates suffer the worst from this. A date in A2 displayed as 3/15/2026 will concatenate as 45736 (the serial number Excel actually stores). Always wrap dates with TEXT.

TEXTJOIN: The Function You'll Use Most

If CONCAT is the upgrade from CONCATENATE, then TEXTJOIN is the upgrade from CONCAT. Released alongside Excel 2019, TEXTJOIN solves two problems that haunt every analyst joining lists of values: separators and blanks.

The syntax: =TEXTJOIN(delimiter, ignore_empty, text1, text2, ...). Three arguments matter, and the second one is the magic.

• delimiter — the character (or characters) you want between each value. A comma. A pipe. A line break. Whatever.
• ignore_empty — TRUE or FALSE. When TRUE, empty cells are skipped silently. When FALSE, they show up as consecutive delimiters.
• text1, text2, ... — the cells, ranges, or literal strings you want to combine.

Here's where it gets useful. Say you have a customer record with name, address line 1, address line 2 (sometimes empty), city, state, and ZIP scattered across columns A through F. To build a single mailing-label string, write:

=TEXTJOIN(CHAR(10), TRUE, A2, B2, C2, D2&", "&E2&" "&F2)

That joins everything with line breaks (CHAR(10)), skips any cells that are empty (TRUE), and uses inline ampersands to glue city, state, and ZIP into a single line. Format the destination cell with Wrap Text and you have a clean address block.

TEXTJOIN also handles arrays nicely. If you want to combine cells in Excel where the source values are conditional — only customers in California, for instance — combine it with IF and an array entry: =TEXTJOIN(", ", TRUE, IF(B2:B100="CA", A2:A100, "")). In Excel 365, this just works. In older versions, press Ctrl+Shift+Enter instead of Enter to commit it as an array formula. Powerful stuff once you get the hang of it.

One warning. TEXTJOIN has a hard limit of 32,767 characters per result. Hit that ceiling and you'll get a #VALUE! error. For most people, it's a theoretical limit. But if you're trying to combine a column with 10,000 rows of paragraph-length text, expect to bump into it.

The Ampersand Operator: Old School, Still Useful

Before CONCATENATE, before CONCAT, and long before TEXTJOIN, Excel had the ampersand. The & operator simply glues two text values together, exactly like a plus sign adds two numbers. It is the most fundamental string operation in the program, and there is no version of Excel released in the last 30 years that doesn't support it.

To combine cells in Excel using ampersand, you write =A2&B2. That's it. To add a space between them, =A2&" "&B2.

To build something more elaborate, you just keep stringing ampersands together: ="Mr. "&A2&" "&B2&", "&C2&" ("&D2&")" gives you something like Mr. John Smith, Engineer (San Francisco). It looks ugly. It always looks ugly. But it works, and it's still the right answer when you need to support a colleague who runs Excel 2013 or some ancient corporate build that lost the modern functions in a security policy.

The ampersand also has one practical advantage over the modern functions: it lets you mix calculations into a string without nesting anything. ="Total: $"&ROUND(SUM(B2:B100),2) drops a calculated sum into a sentence. You can do that with CONCAT too, but the ampersand version reads cleaner because each fragment sits at the same level rather than being nested as arguments.

When does ampersand fall down? Two scenarios. First, when you have to combine dozens of cells — typing thirty ampersands is a job for a person who hates themselves. Use CONCAT or TEXTJOIN instead. Second, when you have empty cells you want to skip. The ampersand has no concept of ignoring blanks, so an empty cell shows up as nothing, which is fine, but the surrounding delimiters still appear, leaving you with awkward stray commas or double spaces in the output.

How to Combine Multiple Excel Cells Into One Cell

Combining two cells is trivial. The real question is what to do when you need to combine multiple cells into one cell — say, merging a paragraph of notes spread across ten rows back into a single text block, or building a comma-separated tag list from a vertical column of 50 entries. This is where ranges in CONCAT and TEXTJOIN start to earn their keep.

Vertical range to single cell. If your data sits in A2:A20 and you want all 19 values combined in B2, the cleanest formula is:

=TEXTJOIN(", ", TRUE, A2:A20)

One line. Empty cells skipped. Done. To force a line break between each value instead of a comma, swap the delimiter for CHAR(10) and turn on Wrap Text on the destination cell. Watch your row height adjust automatically.

Horizontal range to single cell. Identical pattern, just swap the orientation: =TEXTJOIN(" | ", TRUE, A2:F2) joins six columns with pipe separators.

2D range to single cell. TEXTJOIN happily eats a rectangular range too. =TEXTJOIN(", ", TRUE, A2:F20) iterates row by row, value by value, and produces a single string. The order is left-to-right within each row, top-to-bottom across rows. If you need a different order, you'll need to transpose the range first or use a more elaborate formula.

Combine multiple cells from different sheets. Reference each sheet explicitly in the TEXTJOIN call: =TEXTJOIN(", ", TRUE, Sheet1!A2, Sheet2!A2, Sheet3!A2). There's no clean way to use a 3D reference (across sheets) inside TEXTJOIN — you have to list each sheet manually. For more than a handful of sheets, Power Query is the right tool.

One last thing on combining many cells: watch the character limit. A single Excel cell maxes out at 32,767 characters, and TEXTJOIN's output ceiling is the same. If you're concatenating long-form text, you'll hit it sooner than you expect.

Combining Cells Without Losing Formatting

One of the most common questions about how to combine data from two cells in Excel is what happens to the formatting when you do. The short answer: the displayed format is discarded, and Excel substitutes the raw underlying value. The slightly longer answer is that you can preserve any format you want, but you have to do it manually using the TEXT function.

Why this matters. Imagine you have an order number in A2 formatted as 00012345 (eight digits with leading zeros), and a date in B2 shown as March 15, 2026. Underneath, A2 is actually the integer 12345 and B2 is the date serial 45736. If you run =CONCAT(A2, " – ", B2), the result is 12345 – 45736. Almost certainly not what you wanted.

The fix. Wrap each cell that has special formatting in the TEXT function with a format code that matches the display:

• Leading zeros: TEXT(A2, "00000000") forces 8 digits with leading zeros.
• Date as words: TEXT(B2, "mmmm d, yyyy") gives March 15, 2026.
• Currency: TEXT(C2, "$#,##0.00") renders $1,234.56.
• Percentage: TEXT(D2, "0.0%") formats a decimal like 0.235 as 23.5%.
• Phone numbers: TEXT(E2, "(000) 000-0000").

Putting it all together: =CONCAT(TEXT(A2, "00000000"), " – ", TEXT(B2, "mmmm d, yyyy")) returns 00012345 – March 15, 2026, which is what most people expected in the first place.

If you're not sure what format code to use, here's the trick. Select the cell whose format you want to preserve, open Format Cells (Ctrl+1), and look at the Custom category at the bottom of the Number tab. The string in the Type box is the format code. Copy it directly into your TEXT formula and you'll match the display exactly.

Combining Cells in Google Sheets and Other Versions

Most of what we've covered translates almost directly to Google Sheets, but there are a few quirks worth knowing if you split your work between the two. CONCATENATE, CONCAT, TEXTJOIN, and the ampersand all exist in Google Sheets and work the same way. CONCAT in Sheets is slightly different — it accepts exactly two arguments rather than a range — so for combining many cells you use TEXTJOIN or the ampersand. CONCATENATE in Sheets does accept ranges, which is a small departure from older Excel behavior.

Google Sheets has one extra useful function that Excel lacks: JOIN. The syntax is =JOIN(", ", A2:A10) and it works almost identically to TEXTJOIN, except there's no built-in option to ignore empty cells (empty cells produce consecutive delimiters). For most workflows, TEXTJOIN is still the better choice in Sheets.

Older Excel versions. If you're stuck on Excel 2016 or earlier, neither CONCAT nor TEXTJOIN is available. Fall back on CONCATENATE or the ampersand. For combining ranges, you'll need to type out each cell reference individually, or build an array formula. Excel for the web has full support for all the modern functions, so the limitation is only in older desktop installs.

Common errors when combining cells:

• #NAME? usually means you're using TEXTJOIN or CONCAT in a version that doesn't support them. Switch to CONCATENATE or ampersand.
• #VALUE! when using TEXTJOIN typically means you exceeded the 32,767-character limit. Break the range into chunks or use Power Query.
• Numbers showing up as raw serials (dates appearing as 5-digit numbers, currency stripped of dollar signs) — wrap the cell in TEXT with a format code.
• Extra commas or double spaces in your output — switch to TEXTJOIN with ignore_empty=TRUE instead of stacking ampersands.
• Formula returns the formula text as a string rather than evaluating — the destination cell is formatted as Text. Change format to General and re-enter.

For the full structured walkthrough of every Excel skill from beginner to advanced, our Excel hub page is the place to start. We cover SUM, VLOOKUP, IF, conditional formatting, pivot tables, and every other formula that comes up in real work.

Which Method Should You Actually Use?

Here's the practical decision tree. If you're on Excel 2019 or later and just need to combine cells with a separator — names, addresses, tag lists, anything where blanks might appear — use TEXTJOIN. It's the most flexible function and it handles the edge cases that would otherwise require nesting IF or filtering manually.

If you're combining a small number of cells (two or three) and want the shortest possible formula, the ampersand operator wins. =A2&" "&B2 is faster to type than any function call and runs in every version of Excel ever shipped.

If you're combining a range of cells (more than four or five) and you don't need delimiters between them, CONCAT is the right tool. It accepts ranges directly: =CONCAT(A2:A20).

If you need a one-time combination and don't want a formula sitting in your workbook forever, Flash Fill with Ctrl+E is the move. Type one example, press the shortcut, done.

If you're cleaning data from multiple tables, workbooks, or external sources — and you want the work to refresh automatically when new data arrives — graduate to Power Query. It's a heavier learning curve but it solves problems that formulas simply can't.

And the one method to almost always avoid: Merge & Center. It's the most visible button on the ribbon and the most dangerous one in a data table. Reserve it for visual headings where there's nothing to lose.

Once you've got the basics of combining cells, the related skills are how to sort data in Excel, the SUM function, and learning when to switch from formulas to a proper pivot table. Each builds on the others, and after a few weeks of regular use the syntax becomes muscle memory.

The Bottom Line on Combining Cells in Excel

If you take only one thing away: combining cells in Excel almost never means clicking the Merge button. It means writing a formula in a new column that joins the contents of two or more cells, while leaving the originals untouched. The function you'll reach for most often is TEXTJOIN. Memorize the pattern =TEXTJOIN(", ", TRUE, A2:F2) and you'll handle 70% of real-world cases without thinking.

The other 30% splits cleanly. Quick two-cell joins go to the ampersand. Whole-range joins without delimiters go to CONCAT. One-off pattern fills go to Flash Fill and Ctrl+E. Multi-table or refreshable merges graduate to Power Query. And the Merge & Center button is reserved for visual headings, never data.

Beyond combining cells, the same logic — use a formula, never destroy source data — applies across every Excel skill. It's the single most important habit that separates spreadsheet hobbyists from people who actually trust their workbooks. Keep the originals. Build new columns. Test on a few rows. Save before doing anything destructive. Boring rules, but they save weekends.