Text to Columns is a feature used to split data from one column into multiple columns based on a delimiter or fixed width.
It is commonly used for:
- Splitting full names
- Separating first name and last name
- Dividing comma-separated data
- Cleaning imported data
Example Scenario
If a cell contains:
Ali Khan
You can split it into:
Ali | Khan
Method 1: Using Delimited Option
Steps:
- Select the column
- Go to Data tab
- Click Text to Columns
- Choose Delimited → Click Next
- Select delimiter (Space, Comma, Tab, etc.)
- Click Finish
Common Delimiters
- Space
- Comma ( , )
- Tab
- Semicolon ( ; )
- Custom symbol
Example:
Ali,Karachi,03001234567
Split by comma →
Ali | Karachi | 03001234567
Method 2: Fixed Width Option
Used when data is aligned in equal spaces.
Example:
Ali Karachi 03001234567
Steps:
- Select Fixed Width
- Click where you want to split
- Finish
Important Tips
- Always check preview before clicking Finish
- Data will overwrite next columns if not empty
- You can set column data format (Text, Date, General)
- Make sure there is empty space next to the column
When to Use Text to Columns
- Cleaning imported CSV files
- Separating dates and times
- Extracting phone numbers
- Splitting product codes
Why It Is Important
Text to Columns saves time and improves data organization. It is an essential data cleaning tool in Excel.
It helps convert messy data into structured format quickly and efficiently.