Manually merging columns in Excel can be time-consuming and laborious. Here is an easy and quick way to combine columns in Excel.
Excel is a software commonly used in many businesses. Learning how to use, will help you improve performance and productivity.
Some data management activities in Excel, such as inputting data, are very simple. If you use Excel to store large amounts of data, it’s helpful to understand how to combine columns to combine information.
For example, you have an Excel table and want to combine the First Name and Last Name columns into one or combine many columns such as Street, City, Zip Code, and Country into one “Address” column, separating values with commas, so that they become complete addresses ready to print later.
Unfortunately, Excel doesn’t have a built-in feature to do this. However, you don’t have to do it manually. You can combine columns with simple formulas. This way can save you time while improving the accuracy of your data.
Features of Combining Columns in Excel
Excel, as you know, has the function “Merge Cells”. If you combine blank cells or only one of which contains data, this function will come in handy. However, if you combine two or more cells where each cell contains data, you will get an error message such as the following “Merging cells only keeps the upper-left cell value, and discards the other values“.
Unfortunately, only the data in the first (leftmost) cell is saved when you use this option. So, if you want to combine two or more columns and keep all the data intact at the same time, this is not the right tool for you.
Other Interesting Articles
How to Merge Columns in Microsoft Excel Without Data Loss
If you just want to merge two blank columns in Excel, you can use the “Merge” option to do so, but if the columns contain data, you will lose them all.
In Excel, there are two ways to combine columns:
- Using the Ampersand (&) symbol
- Using the CONCAT function
Using the ampersand symbol is often faster and easier than using the CONCAT function. However, use whichever method you are most comfortable with.
Use the Ampersand (&) symbol to merge the words in Excel
For example, suppose you have data with “First Name” and “Last Name” columns. Then, you want to combine the data of both columns in the “Full Name” column.
Select the column “Full Name” (D2), and then type the formula as follows:
=B2&C2
The formula will combine two columns. And if you want to add a space separating the two columns, you can write the formula as this:
=B2&" "&C2
Then copy and paste the formula into the next line.
Using the CONCAT function to merge problems in Excel
Select the column “Full Name” (D2), and then type the formula as follows:
=CONCAT(B2," ",C2)
Where B2 and C2 are “First Name” and “Last Name” respectively. Note that there is a space between quotation marks ” ” in the formula. This is the separator that will be inserted between the merged names, you can use other symbols as separators, e.g. commas.