How-To

How to Combine Two Columns in Excel

By Adam Davidson

Published June 28, 2023

Do you need to merge data from different columns in your spreadsheet? Here’s how to combine two columns in Excel.

Microsoft Excel is a powerful tool for manipulating and analyzing data. When you’re pulling data from outside sources, however, you may find that although you have the data that you want, it’s not in the format that you need.

For example, if you pull a list of names, these may be split into a column of first names and a column of surnames, when you really want to have the whole name in one cell.

The good news is that it’s quick and easy to take the contents of two or more columns and put them together. Here’s how to combine two columns in Excel.

How to Combine Columns in Excel Using Ampersands

If you want to combine data from different columns, one of the easiest ways to do so is by using the ampersand (&) symbol. This symbol allows you to create a formula referencing multiple cells that combines the data from each of them.

You can also use ampersands to add additional text or empty spaces. Once you’ve created your formula for one cell, you can quickly apply that formula to an entire column.

To combine columns in Excel using ampersands:

1. Open the spreadsheet containing the columns you want to combine.
2. Select the empty cell where you want your combined columns to appear. If you want to combine multiple rows of cells, select the top cell first.
   
3. In the cell you have just selected, type =
4. Select the cell containing the first piece of data you want to include.
   
5. Type the & symbol.
   
   
6. If you want to include a space or any other character between the two pieces of data, type ” “& or “,”& for example. Otherwise, skip to the next step.
   
7. Select the cell containing the second piece of data you want to include.
   
8. If you want to combine more than two columns, you can repeat steps 5-7 to include as many additional cells as you wish.
9. Press Enter.
10. Your cell should now contain the combined data of the cells that you selected.
    
11. To combine further rows of data in the same way, click and hold the drag handle in the bottom right corner of the cell.
    
12. Drag down over the cells where you want your combined data to appear.
    
13. Let go and these cells will autofill.
    
14. Your newly created cells will show the combined data, but when you click them, you will see the formula that you entered. This is useful for allowing the combined data to automatically update if you change the data in the columns that you’re combining. If you just want to have the text, however, you can replace the formula with the result.

How to Combine Columns in Excel Using CONCAT

Another method of achieving the same result is to use the CONCAT function in Excel. This is an updated version of the original CONCATENATE function.

While the CONCATENATE function is still currently working, it is being deprecated, so it’s best to use the newer CONCAT function instead.

To combine columns in Excel using CONCAT:

1. Open your spreadsheet and select the cell where you want your first combined cell to appear.
2. Type =CONCAT(
   
   
3. Select the cell containing the first piece of data you want to combine.
   
4. If you want to add a space or any other character between your data, type ,” ” or ,”,” for example. Otherwise, move on to step 5.
   
5. Type a comma (,) then select the cell containing the second piece of data you want to combine.
   
6. If you want to combine more than two columns, you can repeat steps 3-5 with as many cells as you want.
7. Type ) and press Enter.
   
8. Your combined data should now appear in the cell.
   
9. To apply the formula to the rest of your cells, click and hold the drag handle in the bottom right corner of the cell.
   
10. Drag down over the cells that you want to fill.
    
11. Let go, and the cells will autofill.
    
12. If you want your cells to contain just the combined data rather than the formula, you’ll need to replace the formula with the result.

How to Create Text By Combining Columns in Excel

The above methods show you how to take data from two cells and put them together in a different cell. However, you can use the same methods to create a brand-new piece of text that includes the data from your selected cells.

This can be useful for generating phrases that include the data from your columns. The method below uses ampersands, but you can achieve the same effect using the CONCAT function.

To create text by combining columns in Excel:

1. Open your spreadsheet and select the cell where you want your new text to appear.
2. Type =”
   
   
3. Enter the first part of your text that you want to include. Make sure you add a space at the end if you need one before the text that you’re about to insert from one of your columns.
   
4. Type “&
   
   
5. Select the cell containing the first piece of data you want to include.
   
6. Type &”
   
   
7. Enter the second part of your text that you want to include. Make sure you add spaces at the start and end of this text if you need them.
   
8. Type “&
   
   
9. Select the cell containing the second piece of data you want to include.
   
10. If you want to add any additional text after this data, type &” and enter this text followed by a final “. You may need to include a space before your text.
    
11. Press Enter and your new text should be created.
    
12. To apply your formula to other cells, click and hold the drag handle in the bottom right corner.
    
13. Drag down over the other cells to autofill them.
    

How to Format Dates When Combining Columns

One issue that can occur when combining columns in Excel is when one of the columns you’re combining contains dates. By default, these dates lose their formatting and get converted into numbers.

In order to display the dates correctly, we have to use the TEXT function to tell Excel how we want those dates to be displayed.

To format dates when combining columns in Excel:

1. Select the cell where you want your combined cells to appear.
2. Type =
   
   
3. For a cell that contains a date, type TEXT(
4. Select the cell containing the date.
   
5. Type ,”
   
   
6. Enter the format of how you want the date to appear. For example, mm/dd/yyyy or dd/mm/yy.
   
7. Type “)&
   
   
8. Add a space or other character if needed with ” “& or similar.
   
9. Select your next cell. Any cells that don’t contain dates can be selected as normal.
   
10. Press Enter and your text should appear with the correct date formatting.
    

Analyzing Your Data in Microsoft Excel

Learning how to combine two columns in Excel allows you to create cells containing exactly the data that you want. This can be useful if you’re pulling data from somewhere else that comes in separate columns, but you would prefer to have it within the same cell.

Excel is such a powerful application that many of us only scratch the surface of what it can do. For example, you can learn how to capitalize text in Excel if you want to use name data that’s currently all lowercase. You can use Excel to calculate someone’s age from their date of birth, or even use the PPMT function to calculate how much of your loan repayments are going towards paying off the principal.