Concept
Works in every Excel version.
1. Delimited — split at a separator
A column contains: Rahul Sharma,9876543210,Delhi
- Select the column.
- Data → Text to Columns.
- Choose Delimited → Next.
- Tick Comma (or Tab, Semicolon, Space, or type your own in Other, e.g.
|). - Next → Finish.
Result: Name, Mobile and City in three columns.
Tip: tick Treat consecutive delimiters as one when data has double spaces or double commas, so you don't get empty columns.
Only combine consecutive delimiters when they are redundant separators. In A,,C, the empty field may be meaningful; combining delimiters would shift C into the wrong column.
2. Fixed width — split at positions
Old software, bank statements and printed reports often line up data in fixed positions instead of separators:
20261001 NEFT RAHUL SHARMA 25000
20261003 UPI NEHA GUPTA 1800
Choose Fixed width → Next. Click on the ruler to add break lines, drag to move them, double-click to remove. Preview shows the result.
3. Step 3: column formats (the important part)
In the last step, click each column in the preview and pick a format:
| Format | Use for |
|---|---|
| General | normal numbers and text |
| Text | PIN codes, product codes, account numbers — keeps leading zeros (00457 stays 00457) and stops long numbers turning into 9.88E+15 |
| Date: DMY | Indian dates like 15/08/2026 (see Module 5, Lesson 5) |
| Do not import column (skip) | columns you don't want |
4. Choose a destination
By default, results overwrite the original column and the columns to its right. Excel asks before replacing data, but it's easy to click OK. Set Destination to an empty cell (e.g. $F$1) to keep the original.
5. Splitting full names
Rahul Kumar Sharma split on Space gives three columns; Neha Gupta gives two. Names don't line up. For names, Flash Fill (Module 5, Lesson 3) or TEXTSPLIT is usually better.
6. Hidden use: fix numbers and dates stored as text
Select one column → Text to Columns → Finish (without changing anything). Excel re-reads every cell, and text-numbers often become real numbers. Choose Date: DMY in step 3 to fix text dates.
Common mistakes
Overwriting neighbouring columns. Losing leading zeros because the column was left as General. Splitting names with a space delimiter and getting misaligned columns.