How to Combine Two Columns in Excel: 5 Best Methods

Looking to merge or combine data columns in a spreadsheet to get a broader picture of the database? Learn below how to combine two columns in Excel.

Excel comes with various formulas and commands that you can use to manipulate data and discover data insights for professional or personal needs. One such functionality of Microsoft Excel is merging columns and rows to merge data from different cells.

When merging or combining text or number entries from one column to another, many questions could arise. For example, the time needed, loss of data if any, loss of formatting, and so on.

Read this article until the end to discover some of the best and smart methods that enable you to make Excel combine two columns of data automatically.

About the Merging & Center in Excel

Let’s say you downloaded a comma-separated value (CSV) file of employee details from the business HR app or portal.

You find that there are many columns of data. It could be possible that merging all the columns like first name and last name, area code and phone number, designation and department, and more into one column makes more sense than individual columns.

It would come to your mind that Excel has a neat Merge & Center function which might come in handy to combine data from different columns in one.

Unfortunately, that function is only for formatting columns and rows. It doesn’t combine the data of the columns and rows being merged. It just simply takes the data from the cell to the top right corner of the cell range being merged.

Then how would you combine two or more columns in Excel automatically without manually typing the data yourself? Find the answers below:

1. How to Combine Two Columns in Excel: Using Flash Fill

Flash Fill uses the AI algorithm of Excel to recognize patterns from adjacent cells and columns and perform the action that you just completed. When you use the Flash Fill command, Excel replicates your movement in milliseconds.

Hence, this is the fastest way to merge multiple adjacent cells’ data into one column. Here are the steps to follow:

• Ensure that the columns you want to merge are next to each other.
• Also, the destination column should be just the next right column. Check the image to understand better.
• Now, for the first cell of the column where you want merged texts or other data, type in the combined entry manually. You just need to do it once.

• Keep that cell selected and then go to the Data tab on the Excel ribbon menu and click Flash Fill located in the Data Tools commands section.
• Excel will copy your moves for the selected cell and combine two or more cell data into one column.

2. How to Combine Two Columns in Excel: Using Ampersand Operator

Ampersand or the & symbol works as an operator in Excel. Here’s how you can use this symbol to combine multiple columns into one column in Excel:

• Go to the cell where you want merged data from two or more cells.
• Type equals (=) to initiate a formula.
• Use the arrow keys to select the first cell and put an & symbol.
• Now select the next cell and put another &.
• Do the same for any successive cells that you want to merge.

• Hit Enter.
• You should now see that the ampersand operators have merged multiple cells into one cell.
• Now, copy the formula and select a range of cells in the column and paste it.
• You’ll see that Excel applied the formula of the first cell of the combined data column to all the cells that you’ve selected.

Here, you must note that when joining data or texts using a formula, the combined data cell’s values or entries will change when you change the input data of the formula.

Keep reading to discover a trick to just copy value and paste it without a formula to disallow data alterations when the input cell data changes.

3. How to Combine Two Columns in Excel: Using the CONCAT Formula

You can also use the CONCATENATE or CONCAT formula to join texts or numbers from multiple Excel cells to one cell and then copy the formula across the column. This will enable you to join multiple columns into one column. Here is how it’s done:

• Select the first cell under the column header.
• This is the column header where you want to combine other columns’ data.
• Enter equals (=) and then type CONCAT.

• Start a parenthesis and type in the cell address for the first cell or select that using the arrow keys.
• Now, type a comma (,) and select the next cell that you want to combine.
• Close the parentheses and hit Enter.
• If the formula works, copy and paste the formula into the whole column.
• Excel will source the adjacent data from multiple cells automatically.

That’s it! You’ve successfully combined two or more cells’ data in one column.

4. How to Combine Two Columns in Excel: Apply TEXTJOIN Function

TEXTJOIN’s working is very similar to that of CONCAT, but here you can use a delimiter like a comma, hyphen, space, and so on. Look at the working steps below:

• Select the target cell and enter an equals sign.
• Now, type TEXTJOIN.
• Start a parenthesis.
• Then, you need to choose a delimiter. Let’s use a hyphen as a delimiter.
• To use hyphens, type “-”, and the formula will ask you to choose between TRUE and FALSE. This is to command Excel whether it needs to count empty cells or not.

• Select TRUE, type a comma, and then start adding the cell addresses that you want to combine separated by a comma.
• Close the formula with a parenthesis.
• Hit Enter and you’ll see that Excel has combined the cells in a flash.
• Now, copy the formula you’ve just created in the whole column to merge two or more columns.

5. How to Combine Two Columns in Excel: Using Add-Ins

There are many Excel Add-ins that perform cool tricks like forcing Excel to combine two columns. One such Excel Add-ins is Merge Cells. Here’s how you can get the Add-in and combine two columns in Excel:

• Click the Developer tab on the Excel ribbon menu.
• Select the Add-ins icon and then choose Store.
• In the Search box, type Merge and hit Enter.
• Merge Cells Excel add-in will show up.

• Select the Add button to install the add-in in the Excel desktop app.
• Once installed, go to Developer and again select Add-ins.
• Now, Merge Cells will show up as your add-ins. Select the tool.
• It should open as a right-side navigation menu on the worksheet.
• Now, the tool will offer three data combining options: Merge by row, Merge by column, and Merge into one cell.
• Choose any and then select a delimiter under the Separate values option. You can choose between Space, Line Break, and Other.

• In the Options field, choose Clear processed cells.
• Now, for the Place the results option, choose the Top-Left cell.
• That’s it! Hit the Merge Cells button to combine one or more columns into one column.

Formatting Tips When Combining Columns in Excel

Keep Values Only

The downside of using formulas to combine two columns in Excel is the values of the cells where you have combined data, will change when you update the data in the related cells.

Hence, you must apply this formatting in Excel to keep values only. Here’s how it’s done:

• Once you’ve got the combined data in a column, select the entire column.
• Press Ctrl + C to copy the content of the columns.
• Now, press Ctrl + V to paste the same data on the same cell ranges.

• But this time, click the Paste Options icon and then choose Values under the Paste Values section.
• Now, Excel will only retain values in the combined cell area and not the formulas.

How to Display Number Formats After Combining Columns in Excel

If the target cell removes formatting of numbers, dates, or currencies, you can use the TEXT function to keep number formatting. Check out the example steps below:

• Begin with the equals (=) sign to start a new formula.
• Choose the starting cell, enter an ampersand symbol (&), and add any delimiter that you want like “-”.
• Now, enter another & symbol and type TEXT.
• Double-click on the TEXT formula suggestion that pops up.
• Then, select a value for the TEXT function. The value should be the second cell that contains a number.

• Enter a comma and then dictate the number format. For date, use “dd/mm/yyyy”.
• Finish typing the formula by entering a parenthesis. Here’s the complete formula:

=A2&"-"&B2&"-"&TEXT(D2,"dd/mm/yyyy")

• Hit Enter and two cells will be combined with no loss of number formatting.

How to Combine Two Columns in Excel With a Space

When combining cells using any formula like CONCAT or ampersand operator, you can add a delimiter, like space. To add a space between two cells in the target cell, simply place the following code in between the cell addresses:

&" "&

An example of a complete Excel function with space as a joiner is as below:

=A2&" “&B2&” "&D2

How to Combine Two Columns in Excel With a Comma

Let’s consider you need to combine the content of multiple columns in one column while separating the content by a comma.

Follow any of the above methods mentioned in the CONCAT or ampersand section. Simply, place the following code between each cell address:

&","&

The complete formula will look like this as shown below:

=A2&","&B2&","&D2

Conclusion

So far you’ve discovered five different methods to merge two or more columns in Excel into one column. Also, you’ve explored some formatting tips and tricks to make the combined column look logical and understandable.

If I missed any method to merge multiple columns into one column in Excel, do mention the method below in the comment box.

You might also like, a bite-sized tutorial to make a drop-down list in Excel and ways to fix arrow keys not working in Excel.