Excel: Split Text to Columns
Split names in one column into two columns
Do you have a spreadsheet where a column contains text that you would like to split into multiple columns? If so, the Convert Text to Columns wizard is for you.
Scenario: You have a column containing first and last names of employees but you would like to split the two names into separate columns.
Overview: When you use the Excel Convert Text to Columns Wizard a dialog box appears that guides you through three easy steps. Step 1 is used to identify whether the data you are splitting is delimited or fixed width. The default is delimited, simply meaning that your data is separated with either a space, perhaps a tab, or some other character which you will specify in Step 2 of the wizard. The delimiter is used by Excel to determine where the data should be split. The sample file used in this eMOSTip is currently separated by a space. Step 3 of the wizard is used to specify the data type for the columns (i.e., a number, date), if needed. The default data type is set to General.
Instructions:
Note: The instructions focus on Excel 2007 steps but 2003 users can find this same feature under the Data menu and the same steps for conversion apply.
- Select the names you wish to split into columns
- Click the Data tab (Excel 2007 users). Excel 2003 users click the Data menu
- Click the Text-to-Columns button located in the Data Tools group (see Figure 1 - the first step of the Wizard appears).
- Click the Next button
- Click Space from the Delimiters list (see Figure 2)
- Click Next (see Figure 3)
- Click the Finish button (Note: if you need a data type other than General you will need to specify it before clicking Finish)
When the last step of the Wizard is complete the column of names will be split in two columns (see Figure 4).
Member of: American Society for Training and Development
National Speaker's Association |
