You can take text in one column and split it into two or more columns.
This might be useful if you have a column of query results that has employee first name and last name in the same column. Splitting the data into two columns allows you to filter by either first or last name.
In this article, you will learn the steps to split text in one column into two different columns in an Excel worksheet.
For this example, you will split a column that contains employee names into two separate columns, one for first name and one for last name.
In order to split text into two columns, you will need to insert a new blank column to which you want to move the data. Otherwise, an existing column will be overwritten with the text from the source column.
To insert a column, right-click the column header to the right of the one you want to split.
Example shown here: split the employee names in column C into two columns. To insert a blank column next to column C, right-click the D column header.
A pop-up menu is displayed.
Click the Insert option.
A new blank column is displayed.
Select the appropriate source column that contains the data you want to split.
Example shown here: C column header.
With the source column selected, click the Data tab within the ribbon menu.
Click the Text to Columns... button.
The Convert Text to Columns Wizard is displayed with a preview of the selected data. From the wizard, choose the type of data that best describes the data you want to split into a different column.
By default, the Delimited option is checked, which means the data in the column you want to split is separated by a type of delimiter, such as a comma.
With the appropriate data type selected, click the Next > button.
Next, select the appropriate delimiter(s) that your data contains, then click the Next > button.
Example shown here: Accepted the preselected delimiters (Tab and Space) clicked the Next > button.
Select the data format and appropriate destination column(s) where you will send your data, then click the Finish button.
Example shown here: Accepted the preselected Column data format (General ) and the Destination (which is autopopulated based on the cells you selected), clicked the Finish button.
A pop-up window appears asking if you are sure you want to overwrite the data in the selected column.
Click the OK button.
The selected data has been split between the source and destination columns. Adjust the column names as needed