How to Merge Two Columns in Excel — Every Method Explained

What Happens When You Merge Two Columns in Excel?

Before we dive into the methods, it's important to understand what "merging two columns" actually means — because Excel treats it differently depending on what you need:

• Layout merge — physically combining two column cells into one wider cell using Merge & Center. This is for formatting only and will delete one column's data.
• Data merge — combining the content of two columns into a third column using formulas, Flash Fill, or tools. This preserves all your data.

For most real-world use cases, you want a data merge — and that's what this guide focuses on.

---

Method 1: Merge Two Columns Using the Ampersand (&) Operator

The ampersand operator is the simplest and most widely supported formula method. It works in all versions of Excel — 2010, 2013, 2016, 2019, 2021, and Microsoft 365.

Formula syntax:

=A2&" "&B2

How to use it:

1. Click on cell C2 (or any empty column)
2. Type =A2&" "&B2 and press Enter
3. The result will be the content of A2 and B2 joined with a space
4. Click on C2, then drag the fill handle (small green square at bottom-right) down to apply the formula to all rows

Example:

| A (First Name) | B (Last Name) | C (Formula Result) | | John | Smith | John Smith | | Sarah | Johnson | Sarah Johnson | | Michael | Williams | Michael Williams |

Customizing the separator:

You can use any separator between the two values:

=A2&", "&B2        → Smith, John
=A2&" - "&B2       → John - Smith
=A2&"@"&B2         → John@Smith
=A2&B2             → JohnSmith (no separator)

Best used for: Quick column merges in any Excel version. Perfect for names, addresses, product codes.

---

Method 2: Merge Two Columns Using CONCAT Formula

The CONCAT function is the modern replacement for the older CONCATENATE function. It's cleaner to write and supports ranges as well as individual cells.

Formula syntax:

=CONCAT(A2, " ", B2)

Extended example — merging three columns:

=CONCAT(A2, " ", B2, ", ", C2)

This would produce: John Smith, New York

TEXTJOIN — the most powerful concat formula:

If you need to merge many columns with the same separator, TEXTJOIN is even better:

=TEXTJOIN(" ", TRUE, A2, B2, C2)

• First argument — the separator (space in this case)
• Second argument — TRUE means ignore empty cells
• Remaining arguments — the cells or range to join

Example:

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

This merges all five columns in a row, separated by commas, and skips any blank cells automatically.

Best used for: Merging multiple columns at once, handling blank cells gracefully, Microsoft 365 and Excel 2019+.

---

Method 3: Merge Two Columns Using Flash Fill

Flash Fill is one of Excel's smartest features. It watches the pattern of what you type and automatically fills in the rest — no formula needed at all.

How to merge two columns with Flash Fill:

1. Create a new column C next to your data

2. In C2, manually type the merged result for the first row

   • Example: if A2 = "John" and B2 = "Smith", type "John Smith" in C2

3. Move to C3 and start typing the result for the second row

4. Excel detects the pattern and shows a grey preview for all remaining rows

5. Press Enter to accept

Keyboard shortcut: Press Ctrl + E after typing the first example to trigger Flash Fill instantly.

Flash Fill with custom formats:

Flash Fill is smart enough to handle complex patterns:

• "John Smith" → normal space merge
• "Smith, John" → last name first
• "J. Smith" → abbreviated first name
• "JOHN SMITH" → uppercase merge
• "[email protected]" → email format

Just type the first example in the pattern you want and Flash Fill will follow it.

Limitation: Flash Fill creates static values, not formulas. If your source data in columns A or B changes later, column C will not update automatically.

Best used for: One-time merges, quick formatting changes, situations where you don't need a live formula.

---

Method 4: Merge Two Columns Using Power Query

Power Query is Excel's built-in data transformation engine. It's available in Excel 2016 and later and is the best option when you need to merge columns as part of a larger data cleaning or import process.

How to merge two columns in Power Query:

1. Select any cell inside your data range

2. Go to Data tab → Get & Transform Data → From Table/Range

3. In the Power Query editor that opens, hold Ctrl and click to select both columns you want to merge

4. Right-click on the selected columns and choose Merge Columns

5. In the dialog box:

   • Choose your separator (Space, Comma, Tab, Semicolon, or Custom)
   • Enter a name for the new merged column
   • Click OK

6. Click Close & Load to bring the result back into your Excel sheet

Advantages of Power Query:

• The merge is repeatable — if new data is added, just click Refresh
• You can merge columns while also doing other transformations (filtering, removing duplicates, changing data types)
• The original columns can be removed automatically
• Works great with large datasets that would slow down formula-based approaches

Best used for: Recurring data imports, large datasets, complex multi-step data transformations.

---

Method 5: Merge Two Columns Using VBA Macro

If you need to merge columns automatically — triggered by a button, scheduled task, or as part of a larger macro — VBA is the right approach.

Basic VBA macro to merge two columns:

vba

Sub MergeTwoColumns()
    Dim lastRow As Long
    Dim i As Long
    
    ' Find the last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row and combine columns A and B into column C
    For i = 2 To lastRow
        Cells(i, 3).Value = Cells(i, 1).Value & " " & Cells(i, 2).Value
    Next i
    
    MsgBox "Columns merged successfully! Results are in Column C."
End Sub

Advanced VBA — merge and replace original columns:

vba

Sub MergeAndReplace()
    Dim lastRow As Long
    Dim i As Long
    Dim separator As String
    
    separator = " "  ' Change this to ", " or "-" as needed
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' Combine into column C
    For i = 2 To lastRow
        Cells(i, 3).Value = Cells(i, 1).Value & separator & Cells(i, 2).Value
    Next i
    
    ' Delete original columns A and B (optional)
    ' Columns("A:B").Delete
    
    MsgBox "Done! Merged data is in Column C."
End Sub

How to run VBA in Excel:

1. Press Alt + F11 to open the VBA editor
2. Go to Insert → Module
3. Paste the code
4. Press F5 to run, or close the editor and run it from Developer → Macros

Don't want to write the code yourself? Our free VBA Macro Generator lets you describe what you need in plain English — like "merge column A and column B into column C with a space separator" — and generates the exact VBA code for you instantly.

Best used for: Automating repetitive merge tasks, processing large volumes of data, integrating column merges into larger Excel workflows.

---

Method 6: Use MergeExcelFiles.org (No Formula or Code Needed)

If you want to merge two columns instantly without writing a single formula or opening the VBA editor, our free browser-based tool is the easiest option.

How to use it:

1. Go to mergeexcelfiles.org/merge-excel
2. Upload your Excel file (drag and drop works too)
3. Select the two columns you want to merge
4. Choose your separator — space, comma, dash, or a custom character
5. Click Merge and download your updated file

Everything happens locally in your browser. Your file is never uploaded to any server, so your data stays completely private. No signup required. Works on Chrome, Edge, Safari, and Firefox on both desktop and mobile.

Best used for: Quick no-code merges, users who don't want to use formulas, merging columns across multiple files.

---

How to Merge Two Columns in Excel Without Losing Data

This is the most important thing to understand. If you use Merge & Center from the Home tab to merge two columns, Excel will only keep the data from the left column and permanently delete everything in the right column. There is no undo warning.

The safe way to merge two columns without losing data:

Step 1 — Add a new column C and use the formula:

=A2&" "&B2

Step 2 — Copy all the results in column C

Step 3 — Paste Special → Values only (Ctrl + Shift + V or right-click → Paste Special → Values) This converts the formula to static text so the values won't disappear when you delete columns A and B.

Step 4 — Delete columns A and B

Now you have one merged column with all data from both original columns preserved.

---

How to Convert the Formula Result to Plain Text

After merging with a formula, your column C still contains a formula (=A2&" "&B2). If you delete columns A or B, the formula breaks. Here's how to convert it to plain text permanently:

1. Select all cells in column C with the merged results
2. Press Ctrl + C to copy
3. Right-click on the same selection
4. Choose Paste Special → Values (or press Ctrl + Shift + V)
5. Click OK

The formula is now replaced by the actual text value — safe to use even if you delete the source columns.

---

Merging Columns With Conditional Logic

Sometimes you only want to merge columns if both cells have values — if one is blank, you want to skip the separator. Use this formula:

=IF(AND(A2<>"", B2<>""), A2&" "&B2, IF(A2<>"", A2, B2))

This formula:

• Merges A2 and B2 with a space if both have values
• Returns just A2 if B2 is empty
• Returns just B2 if A2 is empty
• Returns empty if both are blank

Or use TEXTJOIN which handles this automatically:

=TEXTJOIN(" ", TRUE, A2, B2)

---

Common Mistakes When Merging Two Columns in Excel

Mistake 1 — Using Merge & Center for data Merge & Center is for layout formatting only. It deletes data. Always use a formula or our tool when you need to preserve both column values.

Mistake 2 — Forgetting to paste as values If you leave the formula in column C and delete columns A or B, all your merged data will show as errors. Always paste as values before deleting source columns.

Mistake 3 — Not handling blank cells If some cells in column A or B are blank, a formula like =A2&" "&B2 might produce results like " Smith" (with a leading space) or "John " (with a trailing space). Use TEXTJOIN with TRUE to skip empty cells automatically.

Mistake 4 — Merging inside a formatted Table If your data is in an Excel Table (the kind created with Ctrl + T), merging columns works differently. The formula column becomes a calculated column that applies to the entire table automatically. This is actually useful — but be aware of it.

---

Frequently Asked Questions

How do I merge two columns in Excel without losing data?

Use the formula =A2&" "&B2 in a new column C. Then copy the results and paste as Values only. This preserves the content of both columns. You can then delete the originals safely.

How do I merge two columns in Excel with a comma?

Use =A2&", "&B2 or =CONCAT(A2, ", ", B2). Replace the comma with any separator you need.

How do I merge columns in Excel for the entire column at once?

Type the formula in the first row (e.g., C2), then double-click the fill handle (green square at the bottom-right of the cell) to auto-fill the formula all the way down to the last row of data.

How do I merge two columns in Excel on Mac?

The formula method (=A2&" "&B2) works identically on Mac. Flash Fill (Ctrl + E) also works on Mac Excel. The VBA editor is accessible via Tools → Macros → Visual Basic Editor on Mac.

Can I merge more than two columns in Excel?

Yes. Extend the formula: =A2&" "&B2&" "&C2 or use TEXTJOIN: =TEXTJOIN(" ", TRUE, A2:E2) to merge as many columns as you need with one formula.

How do I merge two columns in Excel and keep both values?

Use the & operator or CONCAT formula — both combine values from two cells into one without deleting either original. Place the result in a new column, then paste as values if you want to remove the formula.

How do I merge columns in Excel using VBA?

Use a For loop: Cells(i, 3).Value = Cells(i, 1).Value & " " & Cells(i, 2).Value. Our free VBA Macro Generator can write this code for you automatically.

What is the difference between CONCAT and CONCATENATE in Excel?

Both do the same thing — combine text from multiple cells. CONCATENATE is the older function (still works in all Excel versions). CONCAT is the newer, cleaner version available in Excel 2019 and Microsoft 365. TEXTJOIN is the most powerful and handles separators and blank cells automatically.

Final Thoughts

Merging two columns in Excel is something you'll do again and again — whether you're combining names, addresses, product codes, or any other data. Now you have six proven methods to handle it in any situation:

• Use & or CONCAT for quick formula-based merges
• Use Flash Fill for instant one-time pattern merges
• Use Power Query for large, recurring datasets
• Use VBA when you need automation
• Use MergeExcelFiles.org when you want zero code and instant results

The most important rule: never use Merge & Center when you need to keep data from both columns. Use a formula or our free tool instead.

Try our free Excel tools — no upload, no signup:

• 🔗 Merge Excel Files — combine multiple workbooks instantly
• 🔗 Split Excel Files — split large workbooks by sheet
• 🔗 VBA Macro Generator — generate macros in plain English
• 🔗 Excel to Word Converter — convert spreadsheets to Word docs
• 🔗 AI Excel Assistant — ask any Excel question, get instant answers