Excel 2016, 2013, and 2010 allow you to parse cells so that you can split data that is contained in one column (e.g., Column A: Last Name, First Name) between two or more columns (Column A: Last Name. Column B: First Name) without entering the data into each individual cell manually. After opening Excel and the data you would like to split, please follow the procedure below.
To Split Data:
- Highlight the column that contains the combined data (e.g., Last Name, First Name) by clicking the letter directly above the column.
- 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.
- Choose “Delimited” > Click [Next].
- 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.
- Check the box next to “Treat consecutive delimiters as one.”
- Click [Next].
- Under “Column data format,” choose “General.”
- Click the red arrow/spreadsheet icon next to the "Destination" text box.
- 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.
- Click the red arrow/spreadsheet icon once more to return to the wizard.
- Click [Finish].
Note: 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.