Use of Text to Column for splitting text in Excel:
While working on excel with bulk data an Excel user may have a situation where he may need to separate the contents of one Excel cell into separate columns or multiple columns.
For example: Let’s take the below Excel Sheet in which the data contained in single-column A needs to be converted. The data format has an address, Town/city, state and PIN Code which the excel user needs to extract in separate columns so that he can use the data as per his requirement like Filter and sort the data.
Follow the steps:
- Select the data Column with Original Data: Highlight 1
- Click on Data tools: Highlight 2
- Click on “Text to Columns” option in the Data Tools tab: Highlight 3
A Dialogue Box named “Convert Text to Columns – Step 1 of 3” appears…
- Select the radio button “Delimited”: Highlight 4
- Click on Next button: Highlight 5
Another Dialogue box named “Text to Columns Wizard – Step 2 of 3” appears:
- In Delimiter, Option select the option Comma which will convert the text separated by commas (,): Highlight 6
Note: Here the selection of Delimiters depends as per the requirement and the Text.
- Click on Next button: Highlight 7
Another Dialogue box named “Text to Columns Wizard – Step 3 of 3” appears:
- In the “Column Data Form” section select the format of a cell where the data will appear. Here “General” is selected: Highlight 8
- Click Finish button: Highlight 9
The Data Preview appears as in the below image:
Now you can see the converted text data into multiple columns.
