Monday, June 13, 2022

How to Combine Two Columns in Excel

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.

[Edit]Steps

[Edit]Using Flash Fill

  1. Know when to use Flash Fill. 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]
    Combine Two Columns in Excel Step 1.jpg
    • 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. Add a blank column next to the columns you want to combine. 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.
    Combine Two Columns in Excel Step 2.jpg
  3. Type the full name into the first cell in column C. For example, if A1 contains and B1 contains , type into C1.
    Combine Two Columns in Excel Step 3.jpg
    • 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 and B1 contains , you could type into column C and Excel should sense the pattern.
  4. Press or . This teaches Flash Fill the pattern.
    Combine Two Columns in Excel Step 4.jpg
  5. Start typing the next combined name into C2. As you type, you'll see that Excel suggest the next combination automatically.
    Combine Two Columns in Excel Step 5.jpg
    • For example, if A2 is and B2 is , Excel will suggest .
  6. Press or . 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.
    Combine Two Columns in Excel Step 6.jpg
    • 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.

[Edit]Using the Ampersand Symbol

  1. Click an empty cell near the columns you want to combine. This should be on the same row as the first row of data in the columns you're combining.
    Combine Two Columns in Excel Step 7.jpg
    • 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. Type an equals sign into the blank cell. This begins the formula.
    Combine Two Columns in Excel Step 8.jpg
  3. Click the first cell in the first column you want to join. For example, if you're combining columns A and B, click A1. This adds the cell address to your formula.
    Combine Two Columns in Excel Step 9.jpg
  4. Type . Place a single space between the two quotation marks. This tells the formula to add a space between the contents of the two columns.[2]
    Combine Two Columns in Excel Step 10.jpg
    • 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. Type another . This time, don't add any quotes or spaces.
    Combine Two Columns in Excel Step 11.jpg
  6. Click the first cell in the second column you want to merge. Now you'll have a formula that looks something like this:
    Combine Two Columns in Excel Step 12.jpg
    • 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:
    • 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. Press or . You've now merged the contents of the two cells at the top of each column.
    Combine Two Columns in Excel Step 13.jpg
  8. Click and drag the formula down the column. This merges the rest of the two columns.
    Combine Two Columns in Excel Step 14.jpg
    • 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. Convert the merged column into plain text. 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:
    Combine Two Columns in Excel Step 15.jpg
    • 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.

[Edit]Using the CONCAT Function

  1. Click an empty cell near the columns you want to combine. This should be on the same row as the first row of data in the columns you're combining.
    Combine Two Columns in Excel Step 16.jpg
  2. Type into the blank cell. This begins the CONCAT formula.
    Combine Two Columns in Excel Step 17.jpg
    • If you're using a version of Excel from before 2019, use instead of .[4]
  3. Click the first cell in the first column you want to join. 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: .
    Combine Two Columns in Excel Step 18.jpg
  4. Type . 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.
    Combine Two Columns in Excel Step 19.jpg
    • You should now have a formula that looks something like this:
  5. Click the first cell in the second column you want to merge and type a closed parenthesis . Now you'll have a formula that looks something like this: .
    Combine Two Columns in Excel Step 20.jpg
    • 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 .
  6. Press or . This creates the formula and joins the two cells at the top of the columns.
    Combine Two Columns in Excel Step 21.jpg
  7. Click and drag the formula down the column. This merges the rest of the two columns.
    Combine Two Columns in Excel Step 22.jpg
    • 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. Convert the merged column into plain text. 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:
    Combine Two Columns in Excel Step 23.jpg
    • 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.

[Edit]References

[Edit]Quick Summary



from How to of the Day https://ift.tt/rxaCTiX

No comments:

Post a Comment