How to merge two columns in excel without losing data

Answer

Merging two columns in Excel without losing data requires a method that combines the contents of both columns into a new column rather than using the standard Merge & Center tool. Using Merge & Center will overwrite all data except the top-left cell.

Method 1: Using the CONCATENATE or CONCAT Function

1. Insert a new column where you want the merged data to appear.

2. In the first cell of the new column, enter the formula:

   • For Excel versions before 2016:

     =CONCATENATE(A1, " ", B1)

   • For Excel 2016 and later:

     =CONCAT(A1, " ", B1)

3. Press Enter. The first row will show the combined data from columns A and B, separated by a space.

4. Drag the formula down to apply it to all rows.

5. Select the new column, copy it, and paste it as Values to remove formulas if needed:

   • Right-click → Paste Special → Values.

Method 2: Using the & Operator

1. Insert a new column for the merged data.

2. In the first cell, type:

   =A1 & " " & B1

3. Press Enter and drag the formula down for all rows.

4. Copy and paste the results as Values to retain only the merged text.

Method 3: Using Flash Fill (Excel 2013 and Later)

1. Start typing the combined data manually in the first row of a new column.

2. Press Enter.

3. Start typing the next row; Excel will suggest the remaining combined data automatically.

4. Press Ctrl + E to accept the Flash Fill suggestions.

Notes

• Using formulas ensures no data is lost from the original columns.

• A space, comma, or other separator can be added between column contents inside the formula. For example:

  =A1 & ", " & B1

• The original columns remain intact for reference or further calculations.

This approach preserves all data and allows flexible formatting without overwriting any existing information.

Do you want me to show a method that merges without creating a new column and still keeps all data?

Yes · 0 No · 0 Add Comment · 0

Add ·

Report