Combining two columns in Excel means joining the contents of two cells into one — and the fastest reliable way is the ampersand formula: click the first cell, type &, add a space in quotes, then click the second cell, so =A2&" "&B2 turns "Ana" and "Ruiz" into "Ana Ruiz". That approach works in every Excel version from 2016 through Microsoft 365. You only need a different tool when you want a line break inside the result, a date without its serial number bleeding through, or two entire ranges stacked rather than joined.

This article covers formula concatenation, the CONCAT and TEXTJOIN functions, Flash Fill, Power Query, and the Text Import/Text to Columns routes, plus the traps that break each one.

What Excel Actually Does When You Combine Two Columns

The & operator and the CONCATENATE-family functions are text operations, so Excel converts every value to a string before joining. A number stays "42" and looks fine; a date stored as 45,000-something turns into that serial number, and a percentage like 0.25 becomes 0.25 rather than 25%. That conversion rule explains nearly every "my formula looks wrong" complaint about combining columns.

The result is static text, not a live relationship. If you later edit cell B2, a formula result updates, but a Flash Fill or Power Query output does not refresh on its own unless you refresh the query. Decide up front whether you need a one-time merge or a repeatable one.

Excel's own documentation on the ampersand operator confirms it returns a text string, and Microsoft's support pages for TEXTJOIN and CONCAT note the same conversion behaviour. Knowing this, treat any date or currency column as something to wrap in the TEXT function before you combine it.

Google Sheets uses the same & operator but transposes range handling in some array formulas, so if you paste a combined-column formula between the two apps, test the first row before dragging it down.

The Ampersand Formula: Two Cells Into One in Under Ten Seconds

This is the method to learn first because it has no menu, no dialog, and no version dependency. It works in Excel 2016, 2019, 2021, and Microsoft 365 on Windows and Mac, and in Excel for the web.

1: Click an empty cell in the same row as your data, for example C2. 2: Type = then click the first cell you want to combine (A2). 3: Type & then a separator: " " for a space, ", " for a comma and space, or "-" for a hyphen. 4: Type & then click the second cell (B2). Your formula now reads =A2&" "&B2. 5: Press Enter, then double-click the small fill handle at the bottom-right of the cell to copy the formula down the whole column.

If the result shows #VALUE!, one of the referenced cells contains an error rather than text. If it shows a stray space at the start, your separator string includes a leading space. To remove the formula and keep the text, select the column, copy it, then use Paste Special → Values.

Failure handling matters here: when you delete the original columns after a Paste Special, any formula still pointing at them returns #REF!. Paste as values before deleting the source columns, and keep a backup copy of the sheet for the first attempt.

CONCAT and CONCATENATE: Which One Survives in Your Version

CONCATENATE was Excel's original join function, and Microsoft has flagged it as a compatibility function kept for older workbooks. CONCAT replaced it and can accept whole ranges, which CONCATENATE cannot. Both live in the Text category on the Formulas tab.

The practical difference shows up when you join ranges. =CONCAT(A2:B2) pulls both cells into one string with no separator at all. To get a space between them you must write =CONCAT(A2," ",B2), which is no shorter than the ampersand version. CONCATENATE would require the same explicit separator.

Function Accepts a whole range Available since Best for
& operator No All versions Two or three cells, quick one-offs
CONCATENATE No Excel 2007 and later Opening legacy workbooks
CONCAT Yes Excel 2019, Microsoft 365 Joining a contiguous block
TEXTJOIN Yes, with a delimiter Excel 2019, Microsoft 365 Many cells with one separator

The verdict is simple: if your workbook must open in Excel 2016, use & or CONCATENATE. If everyone is on Microsoft 365, TEXTJOIN does everything CONCAT does, with less typing and a built-in delimiter.

One version-specific caveat: CONCAT and TEXTJOIN do not exist in Excel 2016 or earlier, and opening such a file there returns #NAME?. That error is your signal that the recipient's Excel is older than the function.

TEXTJOIN Handles the Separator and the Blanks For You

TEXTJOIN takes three arguments: the delimiter, a TRUE/FALSE switch for ignoring empty cells, and the range. =TEXTJOIN(" ",TRUE,A2:B2) joins the two cells with a space and skips any blank, so a missing middle name never leaves a double space in the output.

That ignore-empty switch is the feature that saves real work. With the ampersand method, a blank B2 leaves a trailing space or a dangling comma that survives into mail merges and CSV exports. Setting the second argument to TRUE removes the whole problem.

TEXTJOIN also accepts a line break as its delimiter. Type =TEXTJOIN(CHAR(10),TRUE,A2,B2) and the two values stack on separate lines inside one cell. You must then turn on Wrap Text on the Home tab, or the line break is invisible and the cell looks like one run-on string.

Where TEXTJOIN earns its place is width: it joins a whole row of cells with one comma between each, something that would take a chain of ampersands and commas to replicate. For a simple two-column join, the ampersand is still fewer keystrokes.

Dates, Numbers, and Percentages Break Naive Formulas

Wrap any date or number in TEXT with a format code before combining, or the raw serial number appears. =A2&" "&TEXT(B2,"mmmm d, yyyy") produces "Ana March 4, 2025" instead of "Ana 45720". The format code inside TEXT uses the same patterns as the Format Cells dialog.

Percentage columns need the same treatment. A cell showing 25% actually stores 0.25, so an unwrapped join produces "0.25". Use =A2&" "&TEXT(B2,"0%") to keep the percent sign. For currency, TEXT(B2,"$#,##0.00") preserves the two decimals and the dollar sign.

If your regional settings use a comma as the decimal separator, the format code needs the local equivalent, and Excel's error message will not always tell you that. Test one row with the exact regional format before filling down a thousand rows.

The other silent failure is a number stored as text with a leading apostrophe. It joins fine visually but breaks any downstream lookup or SUM, because Excel does not treat it as numeric. Convert those cells with Data → Text to Columns → Finish, which coerces text numbers back to numeric values.

Flash Fill: A No-Formula Merge That Stops Working When Data Changes

Flash Fill watches what you type and fills the rest of the column from your pattern. Type "Ana Ruiz" in C2 next to "Ana" and "Ruiz" in A2 and B2, press Ctrl+E (Cmd+E on Mac), and Excel fills the column with the same pattern. It lives on the Data tab as Flash Fill.

It is the fastest route for a one-time merge with no formula to clean up afterwards. The output is plain text, so nothing breaks when you delete the source columns, and you never see a #VALUE!.

Its weakness is that it is a guess. On irregular data — mixed name orders, inconsistent spacing, some cells with middle initials — Flash Fill fills part of the column and silently leaves blanks or wrong joins. Always scroll to the bottom of a long column and spot-check ten random rows before trusting it.

Flash Fill also does not update. Change a source cell and the filled value stays stale, so it suits a final export, not a working model. I have watched people build a reporting sheet on Flash Fill and then discover the numbers stopped tracking source edits a month earlier.

Power Query Merges Columns Without Touching the Original Data

Power Query is the right tool when the merge must repeat monthly. Select your table, then Data → Get Data → From Table/Range, and the Power Query Editor opens. Hold Ctrl and click both column headers, right-click, and choose Merge Columns; pick a separator from the dropdown, and confirm.

The premium detail worth knowing: Power Query's Merge Columns dialog offers no blank-skipping option, so empty cells produce doubled separators, and you have to clean them afterwards with Transform → Replace Values on the merged column. The alternative is to build the custom column with a formula that checks for blanks first.

Apply and Close sends the result back to a new sheet as a query output. Next month, drop the new data into the source and hit Data → Refresh All. Every merged value recalculates against the new rows, which the ampersand and Flash Fill cannot do without re-doing the work.

The cost is time on the first build: roughly five extra minutes versus typing one ampersand formula, and a steeper learning curve for anyone who has not used the editor. That five minutes pays back on the second monthly run and keeps paying back after that.

Stack Two Columns Into One Long List Instead of Joining Their Text

Sometimes "combine two columns" means appending one list under another, not joining two cells. For that, copy the second column's values and paste them directly beneath the last row of the first column, then delete any header row that lands in the middle of your data.

In Microsoft 365, VSTACK does it dynamically: =VSTACK(A2:A10,B2:B10) returns both ranges as a single column that updates when the source ranges change. VSTACK also works across worksheets, so you can stack a January sheet onto a February sheet without copy-paste.

For older versions, the manual copy-and-paste is the reliable route, and Text to Columns on Data is irrelevant here — do not confuse the two operations. A pasted list keeps duplicate values, so run Data → Remove Duplicates afterwards if you need a unique combined list.

This distinction trips people up because both operations are called "combining columns" in conversation. Joining text (the ampersand path) produces one value per row; stacking produces twice as many rows. Pick based on what the next tool needs — a mail merge wants joined text, a PivotTable wants stacked rows.

Seven Failure Cases That Break a Combined Column

The ugly truth: most combined columns fail after the merge, when someone else uses the output. These are the ones that show up in audits.

1: Error values poison the join. A #N/A anywhere in the source becomes the whole string, and =A2&B2 returns the error rather than skipping it. Wrap with IFERROR around the individual cells, not around the entire formula. 2: Leading zeros disappear. A product code "007" joins as "7" because it was stored as a number, and the identifier is now wrong. Format the source as Text first. 3: Inconsistent separators. Some rows get a space, some a comma, because someone typed the separator by hand. Standardise on one separator and apply it everywhere. 4: Text-formatted numbers fire #VALUE!. Numeric-looking text that also contains letters triggers the error on any arithmetic later, so clean the source before joining. 5: Trailing spaces survive the join. Extra spaces at the end of source cells leave a double space in the result. Run TRIM on the source or on the result. 6: Line breaks hide inside cells. A cell with a line break inside it merges as a multi-line result that looks like two cells. Inspect with Wrap Text off before combining. 7: Formulas get pasted as text. Pasting a combined column into an email or system can drop the formula rather than the value — always Paste Special → Values first.

For the third failure case in the list, there is no error message — the file just looks slightly off. Catching it takes a quick scan of the first twenty rows and a spot-check of the last twenty. That two-minute check is cheaper than the downstream cleanup.

Choose the Method That Fits the Job: A Side-by-Side Comparison

Before you pick, write down three things: how often the merge will run, whether the output must update when source cells change, and which Excel version every person who opens the file is running. Those three answers narrow the choice to one method in most cases.

Method Updates when source changes Works in Excel 2016 Best for
Ampersand & Yes, formula recalculates Yes Two or three cells, quick outputs
CONCAT / TEXTJOIN Yes No (TEXTJOIN needs 2019+) Joining ranges with one separator
Flash Fill (Ctrl+E) No, static output Yes One-time merge of clean data
Power Query Merge Columns Yes, on Refresh Yes (version-dependent) Repeating monthly merges
VSTACK Yes No Stacking two lists into one

If the answer is "run once, and clean data", Flash Fill or the ampersand formula wins on speed. If the answer is "run monthly", Power Query wins despite the setup time. If someone in the chain runs Excel 2016, avoid TEXTJOIN and VSTACK entirely.

The one honest drawback of the ampersand formula: it is fragile against errors and blanks unless you wrap it. A 40-character formula built from ampersands, TEXT functions, and IFERROR is hard to read six months later, which is exactly when Power Query starts looking cheap.

FAQ

Why does my combined column show a number instead of a date?

The date was stored as a serial number, and combining it converts the serial, not the visual date. Wrap it with the TEXT function: =A2&" "&TEXT(B2,"mmmm d, yyyy"). That produces "Ana March 4, 2025" rather than "Ana 45720".

Can I combine two columns and keep a line break between them?

Yes, but use CHAR(10) as the delimiter rather than a space, and then turn on Wrap Text. In Microsoft 365, =TEXTJOIN(CHAR(10),TRUE,A2,B2) skips blanks and inserts the break in one go. Without Wrap Text, the line break is invisible in the cell.

What does #VALUE! mean when combining columns?

It means one of the referenced cells holds an error or incompatible value that Excel cannot convert to text. Click the offending cell and check it isn't a #N/A or a text value used in a subtraction. Wrap each reference in IFERROR if the errors are expected and should not flow into the result.

Is Flash Fill safer than the ampersand formula?

No — Flash Fill guesses the pattern and produces static text, so it does not update when a source cell changes. It is faster for one-time clean data. For any repeating merge, the ampersand formula or Power Query is safer because each result is traceable to a rule.

Do CONCAT and CONCATENATE both exist in Excel 2016?

CONCATENATE exists in 2016; CONCAT and TEXTJOIN do not. Opening a workbook built with CONCAT or TEXTJOIN in Excel 2016 returns #NAME?, so if your colleagues run older versions, stick with CONCATENATE or the ampersand operator for portability.

Can I combine two columns across different worksheets?

Yes — the ampersand formula references other sheets the same way as ordinary formulas, for example =Sheet1!A2&" "&Sheet2!B2. VSTACK also accepts ranges from different worksheets in Microsoft 365. Both approaches break if you rename or delete the source worksheets.

What is the fastest way to delete the source columns after combining?

Paste Special → Values first, so the combined column holds text rather than references, then delete the source columns. Skipping the Paste Special step leaves live formulas that return #REF! the instant the source columns disappear.