Excel: How to Parse Data (split column into multiple)

  1. Top 10 Help Pages
  2. Recent Additions
  3. Instructions
  4. Show All Help Pages
  5. Contact Information
  6. Atomic Learning
  7. CedarInfo
  8. CedarNet
  9. Google Apps
  10. Google Calendar
  11. Google Drive
  12. Google Groups
  13. Google Mail
  14. Google Sites
  15. Microsoft Excel
  16. Microsoft Outlook
  17. Microsoft PowerPoint
  18. Microsoft Word
  19. Miscellaneous
  20. Moodle
  21. Other Software
  22. Personal Computers and Devices
  23. TechStop
  24. University Hardware
  25. Windows

Excel: How to Parse Data (split column into multiple)

how to parse data in Excel spreadsheet

In Excel (2016, 2013, 2010) it's possible to parse data from one column into two or more columns. And you can do it in a few simple steps. Suppose column A contains "Last Name, First Name". Follow these steps to split the data from column A into a "Last Name" column and a "First Name" column. No cutting and pasting necessary!

Open the Excel spreadsheet containing the data you want to split, then:

  1. Highlight the column that contains the combined data (e.g., Last Name, First Name) by clicking the letter directly above the column.
  2. Click the “Data” tab in the ribbon, then look in the "Data Tools" group and click "Text to Columns." The "Convert Text to Columns Wizard" will appear.
  3. In step 1 of the wizard, choose “Delimited” > Click [Next].
  4. A delimiter is the symbol or space which separates the data you wish to split. For example, if your column reads “Smith, John” you would select “Comma” as your delimiter. Select the delimiter within your data.
  5. Check the box next to "Treat consecutive delimiters as one."
  6. Click [Next].
  7. Under "Column data format," choose "General."
  8. Click the red arrow/spreadsheet icon at the far right of the "Destination" text box.
  9. Highlight the columns you wish to contain the split data by clicking the letters directly above the columns (you can choose columns from anywhere within the spreadsheet). Or, manually click and drag to select the sells you wish to contain the split data.
  10. Click the red arrow/spreadsheet icon once more to return to the wizard.
  11. Click [Finish].

If the data you wish to split does NOT contain a delimiter (dash, comma, tab etc.) to separate the data, select "Fixed width" within the first step of the "Convert Text to Column" Wizard. This option allows you to manually created divisions within your data by dragging a break line.

Keywords:  split columns, parse data, split cell, separate information

Posted in Computer Help