How To Merge Two Columns In Excel Without Losing Data

You can watch a video tutorial here.

Excel has many tools for formatting cells both for their contents and their appearance. When formatting tables, you may need to merge columns to improve the appearance of the table or to make it easier for readers to understand it. If the columns contain data, it is better to merge the data into a new column instead of using the merge tool of Excel that retains only the top left cell’s data. To merge the data, we will use the CONCAT() function of Excel.

1. CONCAT(): this joins pieces of text together
a. Syntax: CONCAT(text1, text2……)
i. text1, text 2…..: the pieces of text to be joined; at least one piece is mandatory

Step 1 – Concatenate the data

– Select the cell in which the result is to appear
– Type the formula using cell references:
= CONCAT(First Name,“ “, Last Name)
– Press Enter
Note: The “ “ introduces a space between the names

Step 2 – Copy the formula and check the result

– Using the fill handle from the first cell, drag the formula to the remaining cells
OR
a. Select the cell with the formula and press Ctrl+C or choose Copy from the context menu (right-click)
b. Select the rest of the cells in the column and press Ctrl+V or choose Paste from the context menu (right-click)
– The First and Last names have been merged with a space in between

Step 3 – Replace the formula with values

– Copy the cells with formulas
– Open the Paste Special window by right-clicking and selecting Paste Special from the context menu
OR
Go to Home > Clipboard > Paste > Paste Special
OR
Press Alt+E+S
– Select Values
– Click OK