In Excel you may have text within one cell that you want to split. For example, if a cell contains first and last names you may wish to separate first and last name into different columns.
In Excel 2007 and 2010 the Convert to Columns Wizard uses a delimiter (tab, semicolon, comma, space, or custom) to determine where to separate the cell contents into different columns.
1. Select the cells that you want to convert to columns, as shown below.
2. On the Data tab, in the Data Tools group, click Text to Columns.
3. The Convert Text to Columns Wizard dialog box appears.
Click the Next button.
4. In Step 2, select the delimiter type and clear all other types.
In this example, select space since the space indicates where to separate the cell contents into columns.
Note: The Data preview section displays how the contents of the cell will be split into columns.
5. Click the Next button.
6. In Step 3, click each column in the Data preview section, and then click the specific Data format (General, Text, and Date).
Note: General automatically converts:
- Numeric values to numbers
- Date valued to dates
- Everything else to text.
Therefore, in this example, you may leave General selected or change the data format to Text for each.
7. Click in the Destination field, select a new destination for the separated data. If you do not choose a new destination, the original data will be overwritten.
8. Click the Finish button.
In our example, the first name and last name have been split into 2 columns.
Please do share your thoughts with me in the comments below. Thank you!