How to Merge Two Columns in Microsoft Excel
- Sandy Writtenhouse
- February 10, 2022, 11:00am EDT
Some tasks for taking care of data in Excel are intuitive, like inserting currency. Then there are others that are not, like combining columns. Well show you a few different ways to merge two columns in Microsoft Excel.
You may have imported data from an external location or had someone enter data in your sheet. If you end up with two columns that would work better as one, you can combine them using an operator or a function.
About Merging Columns in Excel
As you may know, Excel has a Merge Cells feature. Unfortunately, when you use this option, only the data in the first (far-left) cell is retained. So if you want to combine two columns and keep all data intact, this is not a feature you want to use.Advertisement
To hold onto the data in two columns, youll merge all data into a third column. Before you begin combining the data in the cells as described below, you should prepare by inserting another column if necessary.
Youll then perform the following steps:
- Merge two cells, one from each column, in the first row.
- Copy the formula to the remaining rows in the merged column.
- Optional: Convert the formulas to static values.
Merge Cells in Columns
You have two ways to initially merge cells before copying their contents: using an ampersand operator or using the CONCAT function. Well cover both.
Method 1: Using the Ampersand Operator
The ampersand symbol (&) has more uses than a shorter way to type the word and. You can use the ampersand operator in a formula to combine data in cells.
RELATED: Functions vs. Formulas in Microsoft Excel: What's the Difference?
Go to the cell in the first row of your new column for the merged data and insert one of the following formulas replacing the cell references with your own.
The Best Tech Newsletter Anywhere
Join 425,000 subscribers and get a daily digest of features, articles, news, and trivia. Sign Me Up! We've got your info - thanks for signing up! We were unable to subscribe you! Please check your e-mail address or contact us for assistance. Signing you up...
To merge the cells A2 and B2, you would enter the following formula and press Enter:=A2&B2
This combines the data in those two cells into one string.
To merge the cells A2 and B2 with a space in the middle, you would use the following formula:=A2&" "&B2
Note the space between the parentheses. This is handy for combining first and last names or words that need a space between.
To merge the cells in A2 and B2 with a hyphen in the middle, you would use this formula:=A2&"-"&B2
This is convenient for merging phone numbers or product identifiers.
After this, you can move on to the next step, copying the formula.
Method 2: Using the CONCAT Function
While merging cells and columns with the ampersand operator isnt difficult, it does require some extra typing. Instead, you can use the CONCATfunction or the olderCONCATENATE function.
RELATED: How to Combine Text from Multiple Cells into One Cell in Excel
Go to the cell in the first row of your new column for the combined data and use one of the following formulas. Remember to replace the cell references with your own.Advertisement
To merge the cells A2 and B2, you would enter the following formula and press Enter:=CONCAT(A2,B2)
The result is the same as the first ampersand formula above. You have one string without spaces.
To merge the cells A2 and B2 with a space in the middle, you would use the following formula:=CONCAT(A2," ",B2)Advertisement
With this formula, you can place a space between text or numbers. Note the space between the parentheses.
To merge the cells in A2 and B2 with a hyphen in the middle, you would use this formula:=CONCAT(A2,"-",B2)
Again, this is useful for numbers or even words that contain dashes.
Now that youve merged two cells in a row of your two columns, its time to copy the formula.
Copy the Formula
Once you have the first formula inserted, you dont have to enter it manually for each row in your column. Simply copy the formula down.Advertisement
Select the cell containing the formula. When the Fill Handle (plus sign) appears on the bottom right corner of the cell border, double-click. This copies the formula as far down the column as there is data, which is ideal for columns that span many, many rows.
Alternatively, you can drag the plus sign as far as you need to, rather than double-clicking it. You might use this option if you want to stop at a certain point in the column.
Optional: Convert the Formulas
One final step you may want to take is to convert the formulas to static values. If you plan to delete the original columns and keep only the merged column, you mustconvert the data first. This is because the formulas use the cell references in those columns to combine the data.So, if those cell references no longer contain the data, you wont have the merged data.Advertisement
If this is a step youre interested in, take a look at our full tutorial for converting formulas to static data in Excel.
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage. Read Full Bio »