How to Merge 2 Columns in Excel: Every Method Explained (2026)

Method 1: Combine Two Columns Using the Ampersand Operator (Quickest)

Best for: Fast joins of two columns with full control over spacing and punctuation.

The ampersand (&) is the quickest way to combine columns in excel. You can combine two columns in Excel using the ampersand (&) operator by typing a formula like =A2 & " " & B2. The space inside the quotation marks inserts a separator between the two values.

Steps:

1. Click the first cell of your new column, for example column C if your source data is in column A and column B.
2. Type the following formula: =A2&" "&B2
3. Press enter. The first cell now shows the combined result, for example "James Carter".
4. Click the cell again and double click the fill handle (the small square at the bottom-right corner of the cell) or drag it down to apply the formula to the next cell and all rows below.

The formula updates automatically as it is dragged down, adjusting the row references for each row. To add a comma and space instead of just a space, use: =A2&", "&B2. To combine without any separator, remove the quotation marks content: =A2&B2.

Double click the fill handle instead of dragging it. Excel will automatically fill down to the last row of data in the adjacent column, saving time on large datasets.

Method 2: Use the CONCATENATE Function

Best for: Users working in older Excel versions or who prefer an explicit function over the ampersand shorthand.

The CONCATENATE function is a traditional method in Excel for merging text from two or more cells, but it has been replaced by the CONCAT function in newer versions of Excel, which offers more flexibility. It still works in all Excel versions and produces identical results to the ampersand approach.

The CONCATENATE function can be used to merge columns in Excel, for example =CONCATENATE(A2, " ", B2) combines the text from two cells with a space in between.

Steps:

1. Click the first cell in your new merged column.
2. Type the following formula: =CONCATENATE(A2," ",B2)
3. Press enter to confirm.
4. Drag or double click the fill handle to fill down through all rows.

To add more values from other cells, extend the arguments: =CONCATENATE(A2," ",B2," ",C2) merges data from three columns.

Method 3: Use the CONCAT Function (Modern Excel)

Best for: Excel 2019, Microsoft 365, and anyone who wants the cleaner replacement for CONCATENATE.

The CONCAT function in Excel allows users to combine text from multiple cells into one cell, using commas to separate the cells and quotation marks to add spaces or other text. It works exactly like CONCATENATE but also accepts ranges, which CONCATENATE cannot do.

Steps:

1. Click the first cell in your new single column.
2. Type: =CONCAT(A2," ",B2)
3. Press enter, then drag the fill handle down through the entire column.

To combine an entire range of cells at once, you can pass a range as an argument: =CONCAT(A2:C2) joins all three cells with no separator. For a separator between each value in a range, TEXTJOIN (covered next) is the better option.

The CONCATENATE function still works in all modern versions of Excel for backward compatibility, but CONCAT is the current recommendation for any new formula you write. Both produce the same result when combining two columns.

Method 4: Use TEXTJOIN to Skip Blanks

Best for: Merging columns where some cells in a column may be empty, and you do not want a trailing space or separator in the result.

The TEXTJOIN function is a powerful tool in Excel that enables users to merge text from multiple cells while allowing the option to ignore blank cells, making it ideal for cleaner data merging. The TEXTJOIN function, available in Excel 2016 and later, allows users to combine columns while ignoring blank cells, using a formula like =TEXTJOIN(" ", TRUE, A2, B2).

The second argument (TRUE) is what controls the skip blanks behaviour. When set to TRUE, any empty cells in the argument list are skipped and no extra separator is added in their place.

Steps:

1. Click the first cell in your new merged column.
2. Type: =TEXTJOIN(" ",TRUE,A2,B2)
3. Press enter and fill down.

In the address merge demo file, rows where the Suite column is empty demonstrate this clearly. Using the ampersand operator on a row with an empty unit column produces "890 Maple Avenue, " with a trailing comma and space. TEXTJOIN with TRUE returns "890 Maple Avenue" cleanly with no trailing characters.

To combine more than two columns, add more cell references as additional arguments: =TEXTJOIN(" ",TRUE,A2,B2,C2) merges multiple columns at once and skips any that are empty.

Method 5: Flash Fill to Combine Columns Without a Formula

Best for: One-time merges where you want a result without writing any formula.

Flash Fill detects patterns and automatically fills data without using formulas. It is one of the fastest ways to combine two columns when the source structure is clear and consistent.

Steps:

1. In the first cell of your new column (for example C2), type the combined result manually exactly as you want it to appear. For first and last names: type "James Carter".
2. Press enter to move to the next cell.
3. Press Ctrl + E (the Flash Fill shortcut). Excel analyses the pattern from the first cell and fills the rest of the column automatically.

Alternatively, go to the Home tab, click Fill in the Editing group, and choose Flash Fill from the drop down menu. You can also access it via Data > Flash Fill.

Flash Fill produces static values, not formulas, so the result does not update automatically if the source data in column A or column B changes. This makes it ideal for a one-time operation but not for a living spreadsheet where data updates regularly.

If Flash Fill does not trigger after pressing Ctrl+E, make sure the pattern cell is directly next to the source columns with no empty cells between them. Also confirm that Flash Fill is enabled under File > Options > Advanced > Automatically Flash Fill.

Method 6: Use Power Query to Merge Columns

Best for: Large datasets, recurring data imports, or situations where the same merge needs to run again on refreshed data.

Power Query is effective for merging columns in large, recurring datasets. It handles the combine operation as a transformation step that can be reapplied whenever the source data changes, without manually rewriting formulas.

Steps:

1. Click anywhere inside your data table.
2. Go to the Data tab and click Get Data > From Table/Range (or From Sheet in some versions). Power Query Editor opens.
3. Hold Ctrl and click to select both column headers (for example First Name and Last Name).
4. Right-click one of the selected column headers and choose Merge Columns.
5. In the dialog, choose a separator (Space, Comma, Tab, or Custom) and give the new merged column a name such as "Full Name".
6. Click OK. Power Query creates a new merged column replacing the two source columns.
7. Click Close & Load to return the result to your worksheet as a new table.

The merged result is loaded into a new excel table. If the source data is updated, clicking Refresh in the Data tab re-runs the merge automatically.

Power Query is the right tool when the merge is part of a repeating process. For a simple one-off combine of two columns with only a few hundred rows, a formula is faster to set up.

How to Convert Formulas to Static Values and Delete the Source Columns

After using any formula method (ampersand, CONCATENATE, CONCAT, or TEXTJOIN), the new merged column contains formulas that reference the original columns. If you delete column A or column B, the formulas break. To finalise formula results as static data, copy the new column and paste as values.

Steps:

1. Select all the cells in your new merged column (for example C2:C20).
2. Press Ctrl + C to copy.
3. Right click the first cell of the same column and select Paste Special from the drop down menu.
4. Choose Values (or press V then Enter). The formulas are replaced with static values. The paste special option converts formulas to fixed text that no longer references the original data.
5. Now select column A and column B (hold Ctrl and click each column letter to select both).
6. Right click and choose Delete. Since the merged column now contains static values only, deleting the source columns causes no errors. This step deletes data from the original columns but the merged data in the new column is preserved.

After this process, the single column contains the full name (or any other combined result) as plain text. The original columns are gone and the new merged column stands alone.

Common Issues and Troubleshooting

The merged result shows a number instead of text

This happens when one of the source cells contains a number formatted as a number rather than text. The concatenate function and ampersand both convert numbers to text automatically in most cases, but if the result looks wrong, wrap the number cell in TEXT() to force the format: =A2&TEXT(B2,"0") or =A2&TEXT(B2,"DD-MMM-YYYY") for dates.

There is an extra space or comma at the end of some merged results

This is the blank cells problem. When a cell in the source column is empty, the ampersand formula still adds the separator. Switch to =TEXTJOIN(" ",TRUE,A2,B2) with TRUE as the second argument to skip blanks automatically.

Flash Fill only fills one or two rows instead of the whole column

This usually means the source data has inconsistencies, such as mixed capitalisation, extra spaces, or a row where the two values are in a different order. Check for stray spaces in the source column using the TRIM function, and make sure the pattern is consistent from the first row.

Deleting the original columns breaks the merged column

The merged column still contains formulas referencing the original data. Follow the paste special values step before deleting the source columns. Once the merged column holds static values only, deleting column A and column B is safe.

The CONCATENATE or CONCAT formula shows the formula text instead of the result

The cell is formatted as Text. Click the cell, change the cell format to General in the Home tab number format dropdown, then press F2 and Enter to re-evaluate the formula.

Quick Reference: How to Merge 2 Columns in Excel

For Developers: Combine Column Data Programmatically with IronXL

If your .NET application generates Excel reports where columns need to be merged before delivery, IronXL lets you read, combine, and write cell values in C# without Microsoft Office installed on the server. Rather than writing a CONCAT formula into a cell, IronXL reads the source values and writes the combined result directly as a static value, which means no formula dependencies are left in the output file.

Here is a complete example that loads an employee spreadsheet, combines the First Name and Last Name columns into a new Full Name column, then deletes the original columns:

using IronXL;

WorkBook workBook = WorkBook.Load("employee-list.xlsx");
WorkSheet ws = workBook.DefaultWorkSheet;

// Find how many rows of data exist (starting from row 2, row 1 is the header)
int lastRow = ws.Rows.Count();

// Insert a new column C for the merged result
ws.InsertColumn(2); // inserts before existing column C, shifting data right
ws["C1"].Value = "Full Name";

// Combine first name (col A) and last name (col B) for each row
for (int row = 2; row <= lastRow; row++)
{
    string firstName = ws[$"A{row}"].StringValue;
    string lastName  = ws[$"B{row}"].StringValue;

    // Combine data with a space separator - same result as =A2&" "&B2 in Excel
    ws[$"C{row}"].Value = $"{firstName} {lastName}".Trim();
}

// Save the workbook with the new merged column
workBook.SaveAs("employee-list-merged.xlsx");

using IronXL;

WorkBook workBook = WorkBook.Load("employee-list.xlsx");
WorkSheet ws = workBook.DefaultWorkSheet;

// Find how many rows of data exist (starting from row 2, row 1 is the header)
int lastRow = ws.Rows.Count();

// Insert a new column C for the merged result
ws.InsertColumn(2); // inserts before existing column C, shifting data right
ws["C1"].Value = "Full Name";

// Combine first name (col A) and last name (col B) for each row
for (int row = 2; row <= lastRow; row++)
{
    string firstName = ws[$"A{row}"].StringValue;
    string lastName  = ws[$"B{row}"].StringValue;

    // Combine data with a space separator - same result as =A2&" "&B2 in Excel
    ws[$"C{row}"].Value = $"{firstName} {lastName}".Trim();
}

// Save the workbook with the new merged column
workBook.SaveAs("employee-list-merged.xlsx");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Load("employee-list.xlsx")
Dim ws As WorkSheet = workBook.DefaultWorkSheet

' Find how many rows of data exist (starting from row 2, row 1 is the header)
Dim lastRow As Integer = ws.Rows.Count()

' Insert a new column C for the merged result
ws.InsertColumn(2) ' inserts before existing column C, shifting data right
ws("C1").Value = "Full Name"

' Combine first name (col A) and last name (col B) for each row
For row As Integer = 2 To lastRow
    Dim firstName As String = ws($"A{row}").StringValue
    Dim lastName As String = ws($"B{row}").StringValue

    ' Combine data with a space separator - same result as =A2&" "&B2 in Excel
    ws($"C{row}").Value = $"{firstName} {lastName}".Trim()
Next

' Save the workbook with the new merged column
workBook.SaveAs("employee-list-merged.xlsx")

To replicate the TEXTJOIN skip blanks behaviour in code, add a check before combining:

// Skip blank cells when combining - equivalent to TEXTJOIN(" ", TRUE, A2, B2)
string combined = string.Join(" ",
    new[] { firstName, lastName }
    .Where(s => !string.IsNullOrWhiteSpace(s)));

ws[$"C{row}"].Value = combined;

// Skip blank cells when combining - equivalent to TEXTJOIN(" ", TRUE, A2, B2)
string combined = string.Join(" ",
    new[] { firstName, lastName }
    .Where(s => !string.IsNullOrWhiteSpace(s)));

ws[$"C{row}"].Value = combined;

Imports System.Linq

' Skip blank cells when combining - equivalent to TEXTJOIN(" ", TRUE, A2, B2)
Dim combined As String = String.Join(" ", 
    New String() {firstName, lastName} _
    .Where(Function(s) Not String.IsNullOrWhiteSpace(s)))

ws($"C{row}").Value = combined

IronXL also supports the Merge() method for visually merging cells across rows for header formatting, which is separate from combining column data. For full details, see the merge cells guide and the concatenate Excel files tutorial.

Getting started: Install via NuGet with Install-Package IronXL.Excel. A free trial is available with 30 days of full functionality and no credit card required.

Further reading:

• Merge cells in Excel without losing data
• How to merge cells in Excel with IronXL
• Concatenate and merge Excel files in C#

Wrapping Up

Every approach to how to merge 2 columns in excel comes with a different trade-off. The ampersand operator is the fastest formula for a combine two columns job when every source cell has a value. TEXTJOIN is the better choice when empty cells exist in the data, because its skip blanks argument prevents trailing separators from appearing in the result. CONCATENATE remains fully supported and produces the same output as CONCAT, though CONCAT is the current standard in modern Excel. Flash Fill is the answer when you want a result with no formula at all, delivered by pattern recognition in just two steps.

Once the merged data is in a new merged column, always use paste special values before you delete column A and column B. Converting the formulas to static values first means the new single column is independent and safe to keep as the only remaining result. For recurring workflows on large datasets where the same merge runs every time new data arrives, power query handles the combine with a refresh button rather than rewriting formulas.

Developers working in .NET who need to automate this process in application-generated workbooks can use IronXL to read each row, combine the values in code, and write the merged data back without any formula dependencies. Start with a free trial to test it in your own environment.