How to Combine Two columns in Excel Without Losing Data? Complete Guide
You have a big table and want to merge some of the columns without losing data? This article will explain how to combine two columns in excel with tips and tricks.
Table Of Content
1. How to Combine Two Columns in Excel?
2. How to Put Additional Text Within Combined Cells?
3. How to Put a Space Between Combined Cells?
4. How to Combine Columns with Date Format?
5. How to Get Rid of Formulas in Merged Columns?
6. Bonus Tip: Using Text Files to Combine Multiple Columns
7. Why we are not using merge cells function?
8. Automatic Combinators
9. Conclusion
1. How to Combine Two Columns in Excel?
Basically, there are two ways to combine your columns with formulas easily:
- Concatenate Function
- Ampersand (&) Combinator
Let’s see them both with examples.
Combine columns with CONCATENATE
The CONCATENATE() function is designed specifically for joining text from different cells.
The syntax is very simple: You’ll write all the texts in the formala with the correct order.
Now we’ll combine two columns with this formula:
And you can add also spaces while combining a set of data in columns.
To input a space or any other text or character between values, you’ll also add this to the formula:
So easy, isn’t it?
*NOTE: Excel has introduced CONCAT () function with Excel 2016. This was at first usable for two value combinations. But now CONCAT replaces CONCATENATE function, but Excel still keeps the concatenate to prevent compatibility issues for users of earlier Excel versions.
Ampersand (&) Combinator
Secondly, we’ll do the same thing with the ampersand operator to combine two columns in excel. Maybe, this is a simpler and more flexible way to combine text from different cells.
You’ll input an And symbol, in other words ampersand, between the texts. Also, feel free to add additional texts like space, hyphen, dash, etc.
The formula is again very simple:
Lastly, drag your formula to the whole column to the entire data.
*PRO TIP: While combining your columns, if you're adding any additional text, incuding a space or an hyphen, do not forget to put it between quotation marks.
Now we’ll look at more specific examples of combining columns.
2. How to Put Additional Text Within Combined Cells?
Sometimes, you may need to add extra text when combining cells. Both the CONCATENATE function and the ampersand operator can handle this to combine two columns in excel with additional value.
In the below example, we have first names, last names and job titles in three different columns. So, we’ll now concatenate them into a single column with Full Name – Job Title format.
Here, we are adding our texts in this order: First Name & Space in Quotation Marks & Last Name & Hyphen in Quotation Marks
That’s all.
3. How to Put a Space Between Combined Cells?
To make the combined text more readable, you might want to insert a space between the contents of the two cells. So, this is very common while combining names.
In this concatenate formula, we added a new text as a space between quotation marks.
4. How to Combine Columns with Date Format?
Combining cells that include dates requires special attention to ensure the date format is preserved. So, it will be a bit tricky to combine two columns with date formats in Excel.
Do you know that the date formats are also numbers in Excel?
Yes, they’re looking like dates in different formats, but they are actually numbers.
So, when you concatenate a text with a column in date format, you’ll actually see some meaningless numbers.
The solution is turning your date formula into a text before combining. So, we’ll use Excel TEXT() function here.
This formula will replace the plain numbers with date formats:
Text formula works very simply:
But the configurating the format code might be harder sometimes. You may need text formula not only for dates but also for other specific formats like time, percentage, fraction, special phone numbers or notations. Then, you’ll need to find the right wildcards. Here, you can check Microsoft Office’s text formula explanations to find more examples.
5. How to Get Rid of Formulas in Merged Columns?
After combining columns using formulas, you might want to remove the formulas while keeping the combined text. Maybe, you’ll remove the original individual columns, and this will break your formulas.
In order to avoid this, we’ll simply use copy and paste functionality.
- Combine your columns
- Copy the combined column with CTRL + C
- Right-click on the same column and select Paste Special > Values
Thus, this will replace the formulas with the combined text.
6. Bonus Tip: Using Text Files to Combine Multiple Columns
If you’re working with large datasets or need to combine columns from multiple files, using CSV files can be an efficient approach to combine two columns in Excel.
But, you should be combining adjacent cells with the same delimiter to use this method.
- Firstly, copy your columns and paste it to a text file like notepad
- Secondly, Open Replace wizard with CTRL+H
- Then, replace all spaces with a tab character
- Lastly, copy your notepad values again and paste it to Excel
You can also add space characters or hyphens near the tab character to make a more readable format.
Basically, this is an alternative way to combine multiple columns without using formulas.
7. Why we are not using merge cells function?
Firstly, Excel provides a built-in function to merge cells, which combines the contents of multiple cells into one.
Thus, this may seem the easiest way to combine cells. However, this method will also bring a risk of data loss.
When you highlight two columns and go to merge cells, you’ll receive a warning that you’re going to keep only the upper-left values. And, if you continue with merging, you’ll lose all the data except for the upper-left one:
That’s why you should not use merge cell to combine columns!
So, we always recommend you to use formulas to combine your cells.
8. Automatic Combinators
If you’re working on a very big data sets and you need specific command, our SKU generator tool might be also used as a professional combinator.
>> Download Someka’s Combinator Tool to make Multi-criteria Merges
This tool is basically designed to create sku numbers for inventory management. But it’s also suitable for combining any columns with multi-criteria basis. Just give a try! It’s also available in Google Sheets Combinator format.
9. Conclusion
Finally, in this article we have tried to explain how to combine two columns in Excel. Simply, you can use CONCATENATE function or just & symbol to merge the values in the separate columns into a single column without losing data.
There are some special cases where you’ll need wildcards or special format codes to combine some particular formats like date or time. Lastly, we have also recommended an automatic tool to make complex combinations.
Hope you enjoy this article.
Recommended Readings:
How to Separate Names in Excel?
Related Posts
