Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Merge Two Columns in Excel Without Losing Data

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.

how to merge two columns in excel without losing data


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)

CONCAT function to merge two columns in excel without losing data

  • 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.

copying to notepad to merge two columns in excel without losing data

  • 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.

notepad to merge two columns in excel without losing data

  • 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

ampersand operator to merge two columns in excel without losing data

  • 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.

flash fill tool to merge two columns in excel without losing data

  • 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.

clipboard to merge two columns in excel without losing data

  • 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.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo