Catalog

How to Combine Two Columns in Excel [5 Ways]

August 10, 2023 1.9K views

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.

how to merge two columns with the Flash Fill method

Pros: Convenient and quick

Cons: Fails to handle complex data

Method 2: Use Symbol (&) to Combine Two Columns in Excel

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.

Add Additional Text in an Excel

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

How to combine two cells with space

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")

Correctly Display Numbers in Combined Cells using TEXT function

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”)

Combine cells with  CONCAT Function

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.

Result after merging

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.

Select cells

Step 4 Use Ctrl + C to copy the data to the clipboard.

Step 5 Launch Notepad

Launch Notepad

Step 6 Paste data in it with the selection of Ctrl + V.

Paste the copied cells in Notepad

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.

Replace Tab character with space

Result after replacing all Tab characters with spaces

Step 11 Select data from the Notepad and paste it to the first cell of the column in which you aim to merge data.

Merge cells using Notepad

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.

Formula associated with a cell

Step 1 Click right to the header cell of the combined column and click "Copy."

Select the header and then copy

Step 2 Again, click on the same section, and select Paste and then  Values from the paste option.

Paste by Values

Step 3 Edit what you want, as no formula is left behind.

No formulas left

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.

WPS Office Suit

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.

Avoid duplicate items in WPS Office

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.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.