How To Merge Two Columns In Excel (6 Easy Ways)

When working with a lot of data, we occasionally need to connect two columns in Excel. This tutorial will show you all easy and quick methods on how to merge two columns in Excel  by six easy ways and it will save your time!


Download Practice Workbook

You can download the workbook used for the demonstration from the download link below.


6 Simple Ways to Merge Two Columns in Excel

In this tutorial, we will show you how to merge two columns in Excel in six different ways. It includes the CONCAT function, Ampersand Operator, CONCATENATE function, Clipboard option, Flash Fill feature, and Notepad method. For the purpose of demonstration, we have used the below dataset as a sample.

6 Simple Ways to Merge Two Columns in Excel


1. Using CONCAT Function to Merge Two Columns

You can merge two columns in Excel by using the CONCAT function. It is quite simple to apply in Excel. This function combines the data from multiple strings. This is a newer version of the CONCATENATE function.

Steps:

  • Click in the cell where you want the merged data.
  • Type “=CONCAT(“ and select the first cell you want to merge.
  • Then add a comma and click the second cell you want to merge.
  • After that, close the formula with a parenthesis. We have used the below formula for our sample data.

=CONCAT(B5,C5)

Applying CONCAT function to merge two columns

  • Finally, press Enter and you will get the merged data.
  • Now, select the cell and apply the Fill Handle to the entire column in order to get the merged data of the whole two columns.

Read More: How to Join Two Columns in Excel (5 Easy Methods)


2. Merge Two Columns Through Ampersand Operator

The Ampersand operator is one of the best techniques in order to merge two columns in Excel. It is easier and more convenient to use. You need not to use any function or formula in this method to merge two columns.

Steps:

  • First, select the cell where you want to merge the two columns.
  • Second, type “=” and click on the first cell you want to combine.
  • Third, type & and click on the second cell. We have used the below formula for our sample data.

=B5&C5

  • Finally, press the Enter key and you will get the merged data.

Ampersand Operator to merge two columns

  • Now, select the cell and apply the Fill Handle to the entire column in order to get the merged data of the whole two columns.

Read More: Merge Two Columns in Excel with First Name and Last Name


3. Applying CONCATENATE Function

By applying the CONCATENATE function, you can merge two columns in Excel without losing any data. This is an earlier version of the CONCAT function. But this version will remain compatible with earlier versions of Excel.

Steps:

  • Select a cell in the worksheet.
  • Write the below formula in the formula bar.

=CONCATENATE(B5,””,C5)

Applying CONCATENATE function to merge two columns

Formula Breakdown

  • CONCATENATE(B5,””,C5)
  • Here, the CONCATENATE function joins several text strings into one text string. B5 and C5 are the two cells to be merged. Any space or character can be assigned between the two merged data which you need to write within “”.
  • Now, press Enter.
  • Then, select the cell and apply the Fill Handle to the entire column in order to get the merged data of the whole two columns.

Read More: How to Merge Two Columns in Excel With a Space


4. Merging Two Columns by Clipboard in Excel

By using the Clipboard option, two or even more columns can be merged in Excel. But it is suitable if you want to merge two consecutive columns in Excel. This limits the usability of this method as a completely versatile method.

Steps:

  • From the Home tab, first click the Anchor button at the bottom-right corner of the Clipboard.

Clipboard in Excel

  • Select the two columns you want to merge and press the Ctrl+C keys to copy them.
  • The columns are copied to the Clipboard.
  • Now, click the cell where you want the merged data and press Paste All to paste it.

Merge two columns using Clipboard

  • Now the two columns are merged into one.

Read More: Excel VBA Copy Cell Value to Clipboard (4 Methods)


5. Utilizing Flash Fill to Merge Two Columns

You can use the Flash Fill option to merge two columns in Excel. It is a very easy and simple method. It provides the result automatically if it is turned on. Otherwise, it can be done manually by pressing Ctrl+E.

Steps:

  • First, write the combined text of the two columns in Cell D5.

Utilizing Flash Fill to Merge Two Columns

  • Second, start to write the combined text in cell D6. Now, you can see that Excel is showing the merged data for all the cells.

  • Finally, press the Enter button and you will get the whole merged data.

Read More: How to Merge Columns in Excel (4 Ways)


6. Merge Two Columns in Excel via Notepad

It is a faster method and needs no formulas. But it is suitable only if you want to merge two consecutive columns. Otherwise, the method may not work properly. You also need to use the Notepad app in addition to Excel in this method.

Steps:

  • Select the two columns you want to merge and press the Ctrl+C keys to copy them.

Merge Two Columns via Notepad

  • Open Notepad and press Ctrl+V keys to paste it on the Notepad.

  • Now, press the Ctrl+A keys to select the whole text in Notepad.
  • Again, copy the whole text by pressing the Ctrl+C.
  • Finally, paste the merged data to the desired cell by pressing the Ctrl+V.

Read More: How to Copy Text to Clipboard Using VBA in Excel (2 Easy Ways)


Conclusion

These are all the steps you can follow in Excel to merge two columns. Hopefully, you can now easily create the needed adjustments in columns. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.

For more information like this, visit ExcelDemy.com.


Related Articles

Mehedi Hasan

Mehedi Hasan

Hi, I am Mehedi. I have completed my B.Sc. from Bangladesh University of Engineering and Technology. I have a strong interest in innovation and research in the field of Data Science and Machine Learning. Gradually, I now understand the value of Data Analysis and I am trying to learn everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo