Learn how to use Text to Columns in Excel to split data from one cell into multiple columns. This easy guide includes step-by-step instructions with real-life examples.
Ever received a file where names, addresses, or data are all crammed into one column?
No worries! Excel’s Text to Columns tool is here to rescue you.
What is “Text to Columns”?
Text to Columns is a feature in Excel that lets you break down the contents of a single column into multiple columns using a separator like:
- Comma (
,)- Space (
)- Tab
- Semicolon (
;)- Or any custom character
When Should You Use Text to Columns?
Use it when:
- You have first and last names in one cell and want to split them
- You downloaded a file with CSV data (comma-separated)
- You want to break addresses or email lists
- You need to extract codes, dates, or text parts from a single string
How to Use Text to Columns – Step by Step
Let’s understand with some real-world examples 👇
Example 1: Split Full Name into First and Last Name
| A (Full Name) | B (First Name) | C (Last Name) |
|---|---|---|
| Rahul Sharma | Rahul | Sharma |
| Priya Mehta | Priya | Mehta |
| Suresh Thakur | Suresh | Thakur |
Steps:
- Select the cells (A2:A4)
- Go to the Data tab
- Click Text to Columns (or use shortcuts: Alt+A+E)
- Choose Delimited → Click Next
- Select Space as delimiter → Click Next
- Choose where to place the split (like B2) → Click Finish
Done! First and last names are now in separate columns.
Example 2: Split CSV Data (Comma-Separated)
| A | B | C | D |
|---|---|---|---|
| Riya,Patel,riya@gmail.com | Riya | Patel | riya@gmail.com |
| Manav,Joshi,manav@outlook.com | Manav | Joshi | manav@outlook.com |
Steps:
- Select the column with data
- Go to Data → Text to Columns
- Select Delimited
- Choose Comma (,) → Next → Finish
Note: Great for splitting CSVs or email lists!
Example 3: Split Product Code
Suppose you have codes like PRD-2023-IND
You want:
- PRD (Product)
- 2023 (Year)
- IND (Region)
Use hyphen (-) as delimiter.
| A | B | C | D |
|---|---|---|---|
| PRD-2023-IND | PRD | 2023 | IND |
Pro Tip: Use “Other” option in delimiter and type -.
Types of Split: Delimited vs Fixed Width
| Option | Use When… |
|---|---|
| Delimited | Data is separated by comma, space, tab, etc. |
| Fixed Width | Each section of text is the same number of characters |
Delimited is used in 90% of cases.
Bonus Tip: Always Keep a Backup
Before using Text to Columns, always:
- Make a copy of your data
- Or paste it into a new sheet
- Because it overwrites the original column!
👉 Common Questions
Can I undo Text to Columns?
Yes! Press Ctrl + Z to undo immediately after.
Is Text to Columns dynamic?
No — once split, it doesn’t update if source changes. It’s a one-time action.
Can I use multiple delimiters?
Yes — select Space + Comma or others together.
Final Thoughts
Text to Columns is a hidden gem in Excel that:
- Saves hours of manual typing
- Cleans messy data quickly
- Works like magic when dealing with large lists
Whether you’re a student handling marksheets or a professional cleaning client data — this feature makes life easier in just a few clicks.
What’s Next?
In the next post, we’ll learn about the Remove Duplicates in Excel.