5.8 Splitting Columns
Before
| Location |
|---|
| Kothrud, Pune, 411038 |
| College Road, Nashik, 422005 |
| Dharampeth, Nagpur, 440010 |
After
| Area | City | Pincode |
|---|---|---|
| Kothrud | Pune | 411038 |
| College Road | Nashik | 422005 |
| Dharampeth | Nagpur | 440010 |
Steps in Excel – three ways
- Text to Columns: insert two empty columns to the right › select the column › Data › Data Tools › Text to Columns › Delimited › tick Comma (and Treat consecutive delimiters as one) › in step 3 set Pincode to Text if you want to keep it as a code › Finish. Then TRIM the leading spaces.
- Flash Fill: type Kothrud in B2 › Ctrl + E; Pune in C2 › Ctrl + E; 411038 in D2 › Ctrl + E.
- TEXTSPLIT (Microsoft 365 / Excel 2024):
=TRIM(TEXTSPLIT(A2,","))spills the three parts.
Pincodes shown are fictional-style examples for the areas.
Ravindra Bagale's Tip
Text to Columns overwrites the columns on the right – that's how many students' neighbouring data disappears. Before splitting, insert enough empty columns to the right. And a space remains after the comma, so don't forget TRIM after splitting.
Ravindra Bagale's Tip – मराठी
Text to Columns उजवीकडच्या columns वर overwrite करतो – बऱ्याच students चा शेजारचा data असाच गायब होतो. Split करण्याआधी उजवीकडे पुरेसे रिकामे columns insert करा. आणि comma नंतर space राहते, म्हणून split नंतर TRIM विसरू नका.
Ravindra Bagale's Tip – हिंदी
Text to Columns दाईं तरफ़ के columns पर overwrite कर देता है – बहुत से students का बगल वाला data ऐसे ही गायब हो जाता है. Split करने से पहले दाईं तरफ़ काफ़ी खाली columns insert करो. और comma के बाद space रह जाती है, इसलिए split के बाद TRIM मत भूलना.
Practice task
Split "Area, City, Pincode" for ten stores using all three methods. Compare which one updates automatically when the source changes.