How to merge or combine two columns in Microsoft Excel?

Merge or Combine Two Columns in Excel is an essential skill for data consolidation and customization. By uniting data from separate columns, you can streamline information, improve data integrity, and enhance your Excel proficiency. Whether you’re creating full names, addresses, timestamps, category tags, unique keys, or custom text, mastering the technique of ‘Merge or Combine two columns in Excel’ empowers you to manipulate your data effortlessly. This versatile tool ensures that your spreadsheets are well-organized, and your information is presented in the format that best suits your needs. Embrace this feature to elevate your Excel capabilities and optimize your data management processes.

 Step 1: Here you have two columns, column (A) and column (B). To merge these column’s data into one, select A1 and B1 cell together.

 

Step 2: Select Merge & Center and then click on Merge Cells inside the Home tab.

 

Step 3: You will see an error message saying, “Merging cells only keeps the upper-left value and discards other values”. Click Ok.

 

Step 4: Both the cells are now merged together.

One by one the whole column needs to merge. Using this method will merge or combine two columns together but you will lose the data from the second cell, hence the outcome won’t be a complete one.

How to merge two columns without losing data?

There are several methods of merging columns. While some of these methods will clear the data from the columns you merge together, other methods will combine information from both the columns to provide a complete outcome.

Procedures of merging columns are as follows:

Combining columns using Flash Fill

Flash fill is the simplest and quickest way to efficiently combine data two columns without losing data.

Step 1: To merge the data from column A and B into column C using the flash fill method, first select the C3 cell.

Step 2: Type the data from cell A3 and B3 into cell C3 as given below.

Step 3: Once you hit the Enter key the Flash Fill will detect the pattern.

Step 4: Now start typing the next combined data in C4. As soon as you start typing, the Flash fill will suggest the next combined data automatically.

Step 5: Keep pressing enter in all the columns until all the data from columns A and B are merged into Column C.

Combining columns using Ampersand Symbol (&)

Columns can be combined or merged using “&” very quickly. The only setback is you can can’t merge more than two columns using this method.

Step 1:  Here you have the first and last name of the customers in column A and column B, these data or combined names will be merged into a new column C.

Step 2: Write the below formula in column C, cell C3.

=A3&” “&B3

Step 3: When you hit the Enter key, you will have the data of cell A3 and B3 combined in cell C3.

Step 4: Take your mouse cursor to the right lower corner of C3 and keep it there for two seconds. A (+) icon will appear, now double click on the left button of your mouse.

 

Step 5: Both the columns are now merged without losing any data.

Combining columns using CONCAT or CONCATENATE method

Both of these methods are mostly used by the users well acquainted with excel formulas. You can use these formulas to merge two or more columns without causing any data loss.

Step 1: You have the column A and B as the first and last name columns, now using the CONCAT or CONCATENATE method you will combine the data from column A and B into column C.

Step 2: Write the below formula in column C

=CONCAT(A3,” “,B3)

Or,

=CONCATENATE(A3,” “,B3)

 

Step 3: When you hit the Enter key, you will have the data of cell A3 and B3 combined in cell C3.

Step 4: Take your mouse cursor to the right lower corner of C3 and keep it there for two seconds. A (+) icon will appear, now double click on the left button of your mouse.

Step 5: Both the columns are now merged without losing any data.

Combining columns using Notepad

You can merge columns without losing any data very quickly, using the Notepad.

Step 1: Select all the data from column A and column B and copy them using the Ctrl+C command.

Step 2: Open the notepad and paste the copied data using Ctrl+V command.

Step 3: To select and copy the tab character to the clipboard, first click on the Tab key.

Step 4: Select the tab character, using Ctrl+Shift+LeftArrow command and copy the tab character to clipboard using Ctrl+X command.  

Step 5: Hit Ctrl+H command to activate the replace command box.

Step 6: Paste the copied tab character in Find What box as shown below.

Step 7: Click on the Replace With box and put your desired separator. In the below picture a Space has been put in the box as a separator between the first and the last name.

Step 8: Hit the Replace All button and then Cancel button.

Step 9: Select all the data from the notepad and copy them using Ctrl+C command.

Step 10: Open the worksheet and select C3 cell.

Step 11: Paste the copied data in C3 cell using Ctrl+V command and press Enter key. Both the columns have been merged.

Combining columns using Clipboard

Columns can be combined using the Clipboard option of Microsoft Excel very efficiently without losing any data.

Step 1: Enable the clipboard button by clicking on the clipboard icon as given below.

Step2: Now select all data from both the column A and B. Copy all the data using Ctrl+C command.

Step 3: Double click on the cell you want to put the merged data in as is this instance the C3 cell is selected below.

Step 4: Click on the icon shown below of the copied data and click paste.

Step 5: Now click the Enter key and the data of both the columns will be merged.

Deleting extra columns

If you want to delete column A and column B just to keep the merged data and clear extra columns, you will have to follow some necessary steps given below.

Step 1: Select all the merged data of column C and copy all the data using Ctrl+C command.

Step 2: Right click of mouse on the same column C and select Paste Values (V) under the paste option.

Step 3: Select and delete the reference columns, column A and B from which you got the merged data

Step 4: See the result.

Application of Merge or Combine two columns in Excel

1. Full Names:
   • Merge first and last name columns to create a single column for full names.
2. Addresses:
   • Combine address components like street, city, and zip code into a unified address column.
3. Date and Time:
   • Merge date and time columns to create a timestamp for better analysis.
4. Category Tags:
   • Combine multiple category tags or labels from separate columns into a single category column.
5. Concatenated Keys:
   • Create unique keys by merging values from different columns, often used in database management.
6. Custom Text:
   • Merge columns to add custom text or separators for specific formatting needs.

Merging or combining two columns in Excel simplifies data preparation and organization, making it easier to work with your datasets efficiently and effectively.

For ready-to-use Dashboard Templates:

1. Financial Dashboards
2. Sales Dashboards
3. HR Dashboards
4. Data Visualization Charts