How to Combine Columns in Excel Without Losing Data

PDF download Download Article
With or without formulas, add text from two columns in just a few clicks
PDF download Download Article

Do you want to merge two columns in Excel without losing data? There are three easy ways to combine columns in your spreadsheet—Flash Fill, the ampersand (&) symbol, and the CONCAT function. Unlike merging cells , these options preserve your data and allow you to separate values with spaces and commas. This wikiHow guide will teach you how to combine columns in Microsoft Excel.

How to Combine Two Columns in Excel

  • Use flash fill by combining the data in the two columns in a third column and press Enter to teach Excel the combination pattern.
  • In an empty cell, type = followed by the first cell, then add &" " followed by the second cell to combine them manually.
  • Combine cells using the formula =CONCAT(x," ",y) where x and y are the cells you want to combine.
Method 1
Method 1 of 3:

Using Flash Fill

PDF download Download Article
  1. Flash Fill is the fastest way to combine the values of two columns (such as columns of separated first and last names). You'll teach Flash Fill how to merge the data by typing the first merged cell yourself (e.g., FirstName LastName). Flash Fill will sense the pattern and fill out the rest of the column. [1]
    • The two columns you're combining must be next to each other to use Flash Fill.
    • Don't use Flash Fill if any of the following is true for your data (use the ampersand symbol or the CONCAT function instead):
      • The columns you want to combine aren’t consecutive (e.g., combining columns A and F).
      • You want to be able to make changes to the original columns and have those change automatically update in the merged column.
  2. In this example, let's say column A contains first names, column B contains last names, and that we want column C to contain first and last names combined. If column C isn't blank right now, right click the C column header and select Insert from the menu.
    Advertisement
  3. For example, if A1 contains Joe and B1 contains Williams , type Joe Williams into C1. [2]
    • Flash Fill can detect all sorts of patterns. For example, if column A contains area codes and column B contains phone numbers, you could type the area code and phone number into column C.
    • You can even separate the contents of the columns with words or symbols, as long as you don't make the pattern too hard for Excel to understand. For example:
      • A1 contains the area code 212 and B1 contains 555-1212 , you could type (212) 555-1212 into column C and Excel should sense the pattern.
  4. This teaches Flash Fill the pattern. [3]
  5. As you type, you'll see that Excel suggest the next combination automatically. [4]
    • For example, if A2 is Maria and B2 is Martinez , Excel will suggest Maria Martinez .
  6. This automatically combines the remaining cells from columns A and B into a single merged column C. You don't even have to drag down a formula—the two columns are now merged into one. [5]
    • If the column does not fill, press Control + E on the keyboard to activate Flash Fill manually.
    • You can safely delete the original two columns if you'd like. The new column doesn't contain any formulas, so you won't lose the merged data.
    7. Advertisement
Method 2
Method 2 of 3:

Using the Ampersand Symbol

PDF download Download Article
  1. This should be on the same row as the first row of data in the columns you're combining. [6]
    • Using the Ampersand & is another easy way to combine two columns. You'll create a simple formula using & symbols into the first cell, and then apply your formula to the rest of the data to merge the whole column.
  2. This begins the formula. [7]
  3. For example, if you're combining columns A and B, click A1. This adds the cell address to your formula.
  4. Place a single space between the two quotation marks. This tells the formula to add a space between the contents of the two columns. [8]
    • For example, if column A contains first names and column B contains last names, the " " ensures a space between the first and last names in the new column (e.g., "Joe Williams" instead of "JoeWilliams."
  5. This time, don't add any quotes or spaces.
  6. Now you'll have a formula that looks something like this: =A1&" "&B1 . [9]
    • If you'd rather there not be a space between the words in the merged column, the formula would eliminate the " " and the second ampersand like this: =A1&B1
    • You could also place a symbol, word, or phrase inside of the quotes if you want to insert something between the two joined cells.
  7. You've now merged the contents of the two cells at the top of each column.
  8. This merges the rest of the two columns.
    • You can either click the cell and drag its bottom-right corner to the bottom of the columns, or double-click the square at the bottom-right corner of the cell to use autofill.
  9. Because you used a formula to merge the two columns, the new column is just formulas, not text. If you want to delete the original columns and just keep the merged column, you'll need to do this to avoid losing data:
    • Select all the combined data you've created. For example, C1:C30.
    • Press Control + C (PC) or Command + C (Mac) to copy it.
    • Right-click the first cell in the column you just copied.
    • Select Paste Special and choose Values .
    10. Advertisement
Method 3
Method 3 of 3:

Using the CONCAT Function

PDF download Download Article
  1. This should be on the same row as the first row of data in the columns you're combining.
  2. This begins the CONCAT formula. [10]
    • If you're using a version of Excel from before 2019, use CONCATENATE instead of CONCAT . [11]
  3. For example, if you're combining columns A and B, click A1. This adds the cell address to your formula, which should now look something like this: =CONCAT(A1 .
  4. Typing the first comma separates the first cell from " " , which adds a space between the two values. The second comma prepares you to select the second cell you're merging. [12]
    • You should now have a formula that looks something like this: =CONCAT(A1," ",
  5. Now you'll have a formula that looks something like this: =CONCAT(A1," ",B1) . [13]
    • You could also place a symbol, word, or phrase inside of the quotes if you want to insert something between the two joined cells.
    • Alternatively, if you want the merged text to appear without a space (e.g., JoeWilliams instead of Joe Williams), you could change the formula to =CONCAT(A1,B1) .
  6. This creates the formula and joins the two cells at the top of the columns.
  7. This merges the rest of the two columns.
    • You can either click the cell and drag its bottom-right corner to the bottom of the columns, or double-click the square at the bottom-right corner of the cell to use autofill.
  8. Because you used a formula to merge the two columns, the new column is just formulas, not text. If you want to delete the original columns and just keep the merged column, you'll need to do this to avoid losing data:
    • Select all the combined data you've created. For example, C1:C30.
    • Press Control + C (PC) or Command + C (Mac) to copy it.
    • Right-click the first cell in the column you just copied.
    • Select Paste Special and choose Values .
    9. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      You Might Also Like

      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      How to
      Fix Ethernet Doesn't Have a Valid IP Configuration on Windows
      3 Easy Ways to Freeze Panes to Lock Columns or Rows in Excel
      How to
      Change from Lowercase to Uppercase in Microsoft Excel
      How to
      Make a Spreadsheet in Excel
      How to
      Link Sheets in Excel
      New to Excel? Here's Super Easy Tricks to Get You Started
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7
      2. ↑ https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7
      3. ↑ https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7
      4. ↑ https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7
      5. ↑ https://support.microsoft.com/en-us/office/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7
      6. ↑ https://support.microsoft.com/en-us/office/combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed-b3c3-21340eb164a6
      7. ↑ https://support.microsoft.com/en-us/office/combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed-b3c3-21340eb164a6
      8. ↑ https://support.microsoft.com/en-us/office/combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed-b3c3-21340eb164a6
      9. ↑ https://support.microsoft.com/en-us/office/combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed-b3c3-21340eb164a6
      1. ↑ https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2
      2. ↑ https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2
      3. ↑ https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2
      4. ↑ https://support.microsoft.com/en-us/office/combine-text-from-two-or-more-cells-into-one-cell-81ba0946-ce78-42ed-b3c3-21340eb164a6

      About This Article

      Article Summary X

      1. Add a blank column to the right of the two columns you're merging.
      2. Use Flash Fill to manually type the first combined cell and automatically fill the rest.
      3. Use the & or CONCAT function to create a formula that joins any two columns.

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 129,291 times.

      Is this article up to date?

      Advertisement