How-To

How to Combine Two Columns in Excel

combine columns in excel

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.
    excel select cell
  3. In the cell you have just selected, type =excel equals
  4. Select the cell containing the first piece of data you want to include.
    excel select cell
  5. Type the & symbol.
    excel ampersand
  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.
    excel include space
  7. Select the cell containing the second piece of data you want to include.
    excel select second cell
  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.
    excel combined cells
  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.
    excel drag handle
  12. Drag down over the cells where you want your combined data to appear.
    excel dragged cells
  13. Let go and these cells will autofill.
    excel autofilled cells
  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(
    excel concat function
  3. Select the cell containing the first piece of data you want to combine.
    excel select first cell
  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.
    excel insert space
  5. Type a comma (,) then select the cell containing the second piece of data you want to combine.
    excel select second cell
  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.
    excel concat formula
  8. Your combined data should now appear in the cell.
    excel concat result
  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.
    excel drag handle
  10. Drag down over the cells that you want to fill.
    excel dragged cells
  11. Let go, and the cells will autofill.
    excel autofilled cells
  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 =”
    excel equals
  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.
    excel text
  4. Type “&
    excel ampersand
  5. Select the cell containing the first piece of data you want to include.
    excel select first cell
  6. Type &”
    excel ampersand
  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.
    excel second text
  8. Type “&
    excel ampersand
  9. Select the cell containing the second piece of data you want to include.
    excel select cell
  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.
    excel new text formula
  11. Press Enter and your new text should be created.
    excel new text result
  12. To apply your formula to other cells, click and hold the drag handle in the bottom right corner.
    excel drag handle
  13. Drag down over the other cells to autofill them.
    excel autofilled cells

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 =
    excel equals
  3. For a cell that contains a date, type TEXT(excel text function
  4. Select the cell containing the date.
    excel select cell
  5. Type ,”
    excel quotation mark
  6. Enter the format of how you want the date to appear. For example, mm/dd/yyyy or dd/mm/yy.
    excel date format
  7. Type “)&
    excel ampersand
  8. Add a space or other character if needed with ” “& or similar.
    excel add colon
  9. Select your next cell. Any cells that don’t contain dates can be selected as normal.
    excel select cell
  10. Press Enter and your text should appear with the correct date formatting.
    excel date result

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.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top