Merge Two Columns In Excel or three and more columns Without Losing Data? Often we find data that needs to be combined into one. For example, data on place and date of birth, data on combining employee registration numbers, complete address data and the like.
In MS Excel for certain data processing, the data as above is generally separated and not made into one column. However, for other purposes, this data may have to be combined in one whole in one column.
Well, here, Misnia will share a tutorial on how to Merge Two Columns In Excel Without Losing Data by using data processing tools in MS Excel using formulas.
Why use Formula?
Unfortunately, Microsoft Excel from Microsoft corporation does not have built-in functions that allow you to combine one or more columns without losing data.
So if you use the Merge & Center tool or use the Merge Cells command button, the result is that one of the data in one column will remain and the data in the other column cells will be lost.
If you use the command above, then there will be lost data. So the solution is to use a formula to combine two or more cells in a column without losing any data. How to?
How to Merge Two Columns In Excel Without Losing Data?
Please note there are several formulas that we can use. However, on this occasion, Misnia.com will use one of the formulas in Excel that can be used is CONCATENATE().
Here is a step by step method:
In the following sample, we will give an example to combine two data, namely place and date of birth in excel. The first step is please create or open your data.
Next create a new column and name it as you wish. This column cell will later be used to combine two data or more than two column cells. In the new column enter the formula =CONCATENATE(C2,", ",D2). After that press enter. In the formula, there is a space enclosed in double quotation marks to separate the place of birth data from the date of birth.
How to Merge three Columns In Excel Without Losing Data?
The next tutorial, for example is to combine three data columns "Street", "City" and "province ", into one column without losing data.
Basically the method is exactly the same as the step by step above. Only, in the formula we just add a comma and a space so that there are 2 commas and a space enclosed in double quotes as a separator.
So the formula in the fourth column is =CONCATENATE(C2,", ",D2,", ",E2) then press enter on the keyboard.
See the results as in the image below:
Copying the results of a formula combining two or three columns of cells
To get complete place data, date of birth in one column cell, then you need to change the formula to value so that the column "forge of birth", "date of birth", which is no longer needed can be deleted.
Okay, just as an example, make a selection on cells C2 to C5 and then press Ctrl + C to copy to the clipboard.
Place the cursor in cell E2, Right click on cell E2 to display the shortcut menu and then select Paste Special...
In the Paste Special dialog box that appears, in the Paste group, click the Values radio button and then click the OK button. Now the full name data has been changed from formula to value.
You can now delete the "place of birth" and "date of birth" fields. Highlight or select column headers C and D, right-click the mouse to display the shortcut menu and then select Delete.
That's the tutorial on how to Merge Two Columns In Excel Without Losing Data using Microsoft Excel 2019. You can also merge three, 4, 5 column cells into one column cell with the above trick. Easy isn't it. Happy working.