Easy Way to Combine Columns in Excel
How to Combine Two Columns in Excel? 2 Easy Methods
Note: This tutorial on how to combine two columns in Excel is suitable for all Excel versions including Office 365.
Manually merging columns in Excel can take a lot of time and effort. Here's how to combine two columns in Excel the easy way.
In this article, you will learn:
- How to Combine Columns in Excel Sheets?
- How to Combine Two Columns in Excel With the CONCAT Function?
- How to Combine Two Columns in Excel With the Ampersand Symbol?
- How to Combine Multiple Columns in Excel into One column?
- How to Format Combined Columns in Excel?
- How to Insert a Space Between Combined Cells of the Columns?
- How to Correctly Display Dates and Currency in Combined Cells?
- How to Add Additional Text in Combined Cells?
- How to Remove the Formula from the Combined Columns?
- How to Merge Columns in Excel?
Related:
How To Protect Cells In Excel Workbooks-the Easiest Way
Excel Goal Seek—the Easiest Guide (3 Examples)
Create A Pivot Table In Excel—the Easiest Guide
How to Combine Columns in Excel Sheets?
Let's say, for example, you have two separate columns containing the first and last names of your customers. Now, you want to combine these two columns into a single column that contains the full names of the customers.
There are two methods to go about doing this. You can either use the CONCAT formula method or use the ampersand method. Both of these methods are equally easy to use and I'll break them down one by one in the following sections.
How to Combine Two Columns in Excel with the CONCAT Function?
- Click on the destination cell where you want to combine the two columns.
- Enter the formula: =CONCAT(Column 1 Cell, Column 2 Cell).
Here, replace Column 1 Cell with the name of the first cell of column 1 and Column 2 cell
with the name of the first cell of column 2.
In this example, it is going to look like this: =CONCAT(A2,B2)
- Drag the formula to the entire cell range, as long as you need to.
How to Combine Two Columns in Excel with the Ampersand Symbol?
- Click on the destination cell where you want the combined columns to appear.
- Enter the formula, in this format =Column Cell 1&Column Cell 2
Here, replace Column 1 Cell with the name of the first cell of column 1 and Column 2 cell
with the name of the first cell of column 2.
In this example, it is going to look like this: =A2&B2
- Drag the formula to the data entire range.
Also Read:
Excel Conditional Formatting -the Best Guide (Bonus Video)
The Best Excel Project Management Template In 2021
How To Use Excel Countifs: The Best Guide
How to Combine Multiple Columns in Excel into One Column?
If you want to combine multiple columns in Excel into one column using the above two methods, follow these steps:
- If you are using the CONCAT formula, keep adding the cell references from the extra columns inside the formula. For example, if you want to combine the column C along with columns A and B, the formula would be this: =CONCAT(A2, B2, C2)
- If you are using the ampersand method, keep adding the new cell references in the same format. For example, if you want to combine column C along with columns A and B, the formula would be this: =A2&B2&C2
How to Format Combined Columns in Excel?
While the above methods have technically combined the columns, their results are not always accurate. Let's use the combined full names from the previous example. Let's say that the first name and last name are not separated by a space. In this section, I'll show you how to avoid such errors while combining columns in Excel.
How to Insert a Space Between Combined Cells of the Columns?
To insert a space between two cells of the combined columns, just add a dummy space between the cell references in the formula using the character: " "
For example, if you are using the CONCAT function, it will look like this:
- =CONCAT(A2," ",B2)
Similarly, If you are using the ampersand method, your formula should look like this:
- =A2&" "&B2
How to Correctly Display Dates and Currency in Combined Cells?
If the columns you combine contain any special formatting like dates, currency, or accounting, Excel will automatically strip the formatting before it combines the columns.
To avoid this, you can use the TEXT function to convert this special formatting into text before combining them together.
For example, if you want to combine two dates together to form a date range, use either one of the following formulas:
- =TEXT(A2,"dd/mm/yy")&"-"&TEXT(B2,"dd/mm/yy")
- =CONCAT(TEXT(A2,"dd/mm/yy"),"-",TEXT(B2,"dd/mm/yy"))
In this example, A2 and B2 contain the original dates in proper date format.
How to Add Additional Text in Combined Cells?
Sometimes, you may need to add additional text in between or after the combined cells. To do this, follow the same technique you followed when you added spaces. Insert your custom text in between double quotes.
For example, if you want to add the phrase "was born on" in-between names and date of birth columns, you can use either one of these two formulas:
=CONCAT(A2," expires on ",TEXT(B2,"dd/mm/yy"))
=TEXT(A2,"dd/mm/yy")&"-"&TEXT(B2,"dd/mm/yy")
How to Remove the Formula from the Combined Columns?
The combined column that you created, using the above methods is going to be dynamic. That means any change in the original values will affect the values in the combined column. To prevent this, copy the values of the combined column and paste them as values in the same column, or even a different column.
How to Merge Columns in Excel?
If you don't want to combine the values of two columns, but want to just merge two columns into one instead, you can follow these steps:
- Select the cells or columns that you want to merge.
- Click on the "Merge & Centre" option on the "Home" tab.
Excel will merge the selected columns into one column.
Note: Please keep in mind that this will only keep the value from the upper-left corner cell and clear all other values.
Suggested Reads:
Excel Sumifs & Sumif Functions – The No.1 Complete Guide
Create An Excel Dashboard In 5 Minutes – The Best Guide
Dynamic Dropdown Lists In Excel – Top Data Validation Guide
Closing Thoughts
That's all folks. In this guide, I have shown you how to combine two columns in Excel using the easiest methods. Try using these methods in a practice worksheet and let us know if you have any questions about it.
Want more high-quality guides for Excel? Check out our free Excel resources centre.
Click here to access in-depth Excel training courses and master in-demand advanced Excel skills.
Simon Sez IT has been teaching critical IT software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses by seasoned professionals.
Simon Calder
Chris "Simon" Calder was working as a Project Manager in IT for one of Los Angeles' most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
Source: https://www.simonsezit.com/article/how-to-combine-two-columns-in-excel/
0 Response to "Easy Way to Combine Columns in Excel"
Post a Comment