Merge and Combine Columns without Losing Data in Excel
If you merge multiple columns of data in Excel (no matter which Excel version you are using), only the left column of data will be kept, and the data of other columns will be deleted, if you apply the "Merge and Center" command from Home tab on the Alignment panel. This tutorial is talking about how to merge or combine different columns of data into one column. It's appliable for Microsoft Excel 2010, 2007, 2003, XP (2002) and 2000.
- Method A (4 steps): merge columns of data into one column with formula.
- Method B (1 step): quickly merge columns of date with "Combine" feature of Kutools for Excel.
Method A: Merge multiple columns of data into one column with formula.
To merge cell A1 with cell B1 we woul type the following into cell C1:
=A1&B1
If we want to add spaces between A1 and B1, we need to use the following formula in cell C1:
=A1&" "&B1
Suppose we need to merge columns A ("First Name") and B ("Last Name") and put the result in column C, as shown in the following figure.
Step 1. We would need to type the following formula in cell C2:
=A1&" "&B1
Step 2. Apply the formula to the remaining cells in those columns.
To do this, select the first merged cell and then hover the mouse over the bottom right corner of the cell until you see the plus sign. Drag the cursor downwards until the selection includes all the remaining cells in the merged column. When you release the mouse, the merged column should contain the dat in the first and second columns merged.
Step 3. Copy these merged data and paste to any location you want it to be (click the "123", or "paste special" > "Values").
Step 4. Delete the column A and B. You can enter "Name" or "First Name and Last Name" into A1. It is done.
Method B: Quickly merge columns with "Combine" feature of Kutools for Excel.
You can use the third party add-in Kutools for Excel to quickly merge multiple columns. What is Kutools for Excel?
Step 1. Select the columns of data you want to merge and click "Combine". A dialog will pop up, please specify the options according to what you need, and click "OK". It is done.
Kutools for Excel
More than 100 Advanced Functions for Excel 2007 and 2010
