Excel is a unique tool from Microsoft Office that has made data manage easily and saves records in an organized form. Five different ways are there that end up with merged columns. These include Flash Fill, the CONCAT formula, the ampersand symbol (&), the merge function, and using Notepad.
You may need clarification about the type of method you must adopt. So, in this article, we have discussed five different methods to let you learn how to combine two columns in Excel.
Method 1: Use Flash Fill to Combine Two Columns in Excel
Learning how to combine two columns in Excel, first name and last name, is a straightforward method.
To merge two columns, put the first and last names (Ethan Smith) in cell C2. You can pick the format you like. Now, drag the cursor to the lower-right corner of the Flash Fill handle (+). This practice will merge the two columns leading to a single column with the required data.
Pros: Convenient and quick
Cons: Fails to handle complex data
Method 2: Use Symbol (&) to Combine Two Columns in Excel
Combining columns with ampersand symbols is easy and convenient. Therefore, it needs some basic steps leading to the data in one combined column. So, follow the below steps and be an expert on ampersand symbols.
Step 1 Click the cell in which you want to collect data from different columns
Step 2 Type “ = ”
Step 3 Hit the first cell that you aim to combine
Step 4 Type “&”
Step 5 Hit the second cell that you aim to combine.
Step 6 As the last step, you can press Enter button.
It is a formula-based method. If you want to combine cells B2 and C2, the required formula would be;
=B2&C2
Tips on How to Format Combined Columns in Excel
The combined column may have numbers, text, dates, and other data. Therefore, it is not wise to leave columns without formatting. Consequently, you must know how to combine two columns in Excel with a comma.
We have developed various tips to format combined cells to aid you in this scenario. Throughout this discussion, our preference would be the ampersand method.
Tip 1 Add Additional Text Within Combined Cells
Containing original content in the combined column is not compulsory. Of course, you can add more data to it. But the question is, "How?" Let us understand it with an example.
Let us suppose you want to merge a person's name with his salaryin a constructed sentence. For this purpose, you need to use the formula;
="The Total Salary of "&J2&" is "&H2&""
The speech marks are used to wrap the additional text. Moreover, ignore speech marks when referring to a cell and don't ignore space where it needs as it is compulsory for accurate results.
Tip 2 How to Put a Space Between Combined Cells
Do you have two columns with first name and last name? Now, the question is how to combine two columns in Excel with a space. For this purpose, we use the simple formulas;
=B2&" "&C2
According to the above formula, first, you need to add content for the first columns, followed by that of the second column with the space in between. Moreover, you can put commas, speech marks, dash, text, or any other symbol you need in place of space.
Tip 3 Correctly Display Numbers in Combined Cells
Combined cells with the formatted content in the original may strip formatting. No worries, the TEXT function offers the necessary solution. Suppose a column contains the name with anothercolumn containing date of Joining the job. To merge them, the expected formula may be
=J3&" was born on "&D3
However, it would not offer accurate results. The reason is correctly formatted data gets converted into plain numbers through Excel. At this point, the TEXT function fix this issue. Therefore, the accurate formula is =J3&" joined this job on "&TEXT(D3, "dd/mm/yy")
Pros
Flexibility, You can combine text, numbers, dates, and other data types
Real-time updating.
Cons
Difficulty in handling large datasets
Lack of advanced functionality
Method 3: Use CONCAT/CONCATENATE Function to Combine Two Columns in Excel
Combining two columns with the concat method is complex. So, follow the below steps and merge the columns with confidence.
Step 1 Pick the cell in which you want data to combine and click on it to make it active.
Step 2 In the same column, type“=CONCAT” ( or “=Concatenate(”
Step 3 Now, click the very first cell that you aim to merge.
Step 4 Now, add ,
Step 5: Click the second column you desire to merge.
Step 6 Close the bracket by typing )
Step 7 Hit Enter button from the keyboard.
All done! You have successfully merged columns through the CONCAT formula.
Collectively, for the combination of columns A and B, the required formula would be =CONCAT(A2,”Family”)
Pros：
Convenience
Flexibility
Cons
A bit complex
Method 4: Use Merge Feature to Combine Two Columns in Excel
Combining two columns, row by row, is accessible in Excel. For example, you want to merge all cells in the Details Column, as all cells have the same data.
Step 1: Select all the cells you want to merge.
Step 2: Click the Merge and Center icon.
Remember, this method of combining columns is not recommended as it may lose some data.
Pros: Easy and quick
Cons: Losing of data, Inflexibility
Method 5: Use Notepad to Combine Two Columns
This way of combining columns is simple and does not mean any formula. Of course, you can merge adjacent columns.
Step 1 Click the first cell of the first column.
Step 2 Press Shift + Right Arrow to select the first cell of the adjacent column.
Step 3 Select Ctrl + Shift + Down Arrow. It will highlight all the data for selection.
Step 4 Use Ctrl + C to copy the data to the clipboard.
Step 5 Launch Notepad
Step 6 Paste data in it with the selection of Ctrl + V.
Step 7 Copy the Tab character. You can press the Tab key once in Notepad and then select the space created by pressing the Tab key, and hit Ctrl + C buttons.
Step 8 Open replace dialogue with Ctrl + H. Paste the Tab character in the "Find what" section and space or comma in the "Replace with" field.
Step 10 Choose the "Replace all" button.
Step 11 Select data from the Notepad and paste it to the first cell of the column in which you aim to merge data.
Pros: Easy and simple
Cons: Might be lengthy as compared to other procedures listed above
Method 6: How to Remove the Formula From Combined Columns
Whenever you click a cell the formula associated with that cell appears in the Formula bar. You can remove formulas and keep the original values by performing these steps.
Step 1 Click right to the header cell of the combined column and click "Copy."
Step 2 Again, click on the same section, and select Paste and then Values from the paste option.
Step 3 Edit what you want, as no formula is left behind.
Free Office Suite - WPS Office
WPS Office recommends using WPS Spreadsheetas the best free office software. It allows you to go for all the features free of cost that OpenOffice software offers after purchasing. Moreover, it is versatile enough to be compatible with multiple devices. In addition, with a 4.8-star rating, this software is free of cost with all the features.
Main Features
Free to use
Having a Writer, Spreadsheet, Presentation, and PDF software included in one suite
Compatible with all Microsoft Office file formats
Numerous built-in templates to create and design documents quickly
Available for Windows, Mac, Linux, Android, iOS
Price
Free to use
Supported Devices
Windows, Mac, Linux, Android, iOS
Pros
Extremely light in weigh
Multi-platform support
Easy to use
It comes with advanced features such as a file converter, cloud integration, etc
Cons
The mobile version comes with ads.
FAQs
Q1: How to Combine Multiple Columns in Excel into One Column?
You must use the Concat formula while repeating the formatting to combine multiple columns.
Q2: How to Avoid Duplicate Items After Combining 2 Columns?
WPS Office offers this amazing function of highlighting duplicate items. Yes, you can highlight and then delete duplicate items very quickly.
Summary
Excel offers multiple methods for combining data in a single column. All the ways have some limitations. We recommend you combine columns with WPS Office software as it is free of cost and highly versatile. In addition, you can avoid data duplication with this software.
