Nazmul Hossain Shovon
In this tutorial, I am going to show you 5 easy methods to merge two columns in excel without losing data. You can quickly use these methods even in large datasets. Throughout this tutorial, you will also learn some important excel tools and functions which will be very useful in any excel related task.
Download Practice Workbook
You can download the practice workbook from here.
5 Easy Methods to Merge Two Columns in Excel Without Losing Data
We have taken a concise dataset for this tutorial to explain the steps easily. The dataset has 7 rows and 3 columns. It has the First Name and the Last Name of the customers of an imaginary shop. We will merge these two columns to form a Full Name field.
1. Using CONCAT Function
The CONCAT function in excel concatenates values of different cells that are supplied as references. We can use this function to merge two columns in excel without losing any data.
Steps:
- First, go to cell D5 and enter the following formula:
=CONCAT(B5," ",C5)
- Next, press the Enter key and copy the formula to the cells below using Fill Handle.
- As a result, this will merge the data of the two columns in the new column.
Read More: How to Merge Two Columns in Excel and Remove Duplicates
2. Merge Two Columns Using Notepad
We can use Notepad or any other text editor as an intermediate tool to merge two columns in excel without losing data. Follow the steps below to do this.
Steps:
- To begin with, copy the dataset of the two columns by Ctrl+C.
- Then, open Notepad and paste the two columns using Ctrl+V.
- Now, delete all the space between the two columns and put only a single space in each row.
- After that, copy all the data from the Notepad file.
- Now, go to the excel worksheet and paste the copied data in cell B5.
- Consequently, you should see the two columns are now merged.
Read More: How to Merge Two Columns in Excel With a Space
3. Applying Ampersand Operator
The Ampersand operator joins multiple text strings into a single string. This is a quick method to merge two columns in excel without losing data. Let us see how to do this.
Steps:
- To start this method, navigate to cell D5 and type in the following formula:
=B5&" "&C5
- Then, press Enter.
- Now, copy the formula to all the cells below by Fill Handle and this should merge the two columns.
Read More: How to Join Two Columns in Excel (5 Easy Methods)
4. Utilizing Flash Fill in Excel
The Flash Fill tool in excel is very unique in that it can automatically detect data patterns and fill adjacent cells accordingly. This tool can automate the process to merge two cells in excel without losing data. Let us follow the steps below to use this.
Steps:
- First, manually type the first row of data from the two columns.
- Now, go to Data Tools under the Data tab and select Flash Fill.
- Immediately, excel will fill up the remaining cells below by merging the two columns.
Read More: Merge Two Columns in Excel with First Name and Last Name
5. Using Clipboard in Excel
The Clipboard feature in excel can store all the copied items and allows to use them when needed. We will store the data of the two columns to merge and transfer them to a new column without losing them.
Steps:
- To start with, click on the Clipboard icon as in the image below.
- Now, select the data of the two columns and copy using Ctrl+C.
- As a result, this will add this data to the Clipboard window on the left.
- After that, open a new sheet and double-click on cell B5.
- Moreover, click the drop-down icon on the Clipboard item and select Paste.
- Consequently, excel will merge the two columns of the first sheet and insert them into this new sheet.
Read More: How to Merge Columns in Excel (4 Ways)
Things to Remember
- If you are using an older version of excel and you do not have the CONCAT function, then you can use the CONCATENATE function which will work the same.
- Instead of Ctrl+C, you have to use Cmd+C if you are a mac user.
- You can use the shortcut Ctrl and press the C key twice to copy data to the clipboard.
Conclusion
I hope that you were able to apply the above methods to merge two columns in excel without losing data. Make sure to download the workbook provided to practice and also try these methods with your own datasets. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
