Distribute the contents of a cell into adjacent columns
You can split the contents of one or more cells in a column and distribute those contents as individual parts across other cells in adjacent columns. For example, your worksheet contains a column of full names that you want to split into separate first name and last name columns.
Select the cell, range, or entire column that contains the text values that you want to split.
Note A range that you want to split can include any number of rows, but it can include no more than one column. You also should keep enough blank columns to the right of the selected column to prevent existing data in adjacent columns from being overwritten by the data that will be distributed. If necessary, you can insert blank columns.
On the Data tab, in the Data Tools group, click Text to Columns.
Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.
Note For help with completing all the steps of the wizard, see the topic, Split names by using the Convert Text to Columns Wizard, or click Help in the Convert to Text Columns Wizard.
Tip For an alternative method of distributing text across columns, see the article, Split text among columns by using functions.