5.8 Splitting Columns
Split a column with TEXTSPLIT with example — in 30 seconds
What: TEXTSPLIT cuts one text into several cells at a delimiter such as a comma. TRIM removes the space after each comma.
Why: City, Area and Store ID in one cell cannot be filtered or looked up. Three columns can.
Tiny example: "Pune, Kothrud, BLK-PUN-01" spills into Pune | Kothrud | BLK-PUN-01.
Split a column with TEXTSPLIT with example — 30 सेकंदात
काय: TEXTSPLIT एका text ला comma सारख्या delimiter वर कापून अनेक cells मध्ये टाकतो. TRIM प्रत्येक comma नंतरचा space काढतो.
का: एकाच cell मध्ये City, Area आणि Store ID असेल तर filter किंवा lookup करता येत नाही. तीन columns असतील तर येतं.
छोटं उदाहरण: "Pune, Kothrud, BLK-PUN-01" हे Pune | Kothrud | BLK-PUN-01 असं spill होतं.
Split a column with TEXTSPLIT with example — 30 सेकंड में
क्या: TEXTSPLIT एक text को comma जैसे delimiter पर काटकर कई cells में डालता है. TRIM हर comma के बाद का space हटाता है.
क्यों: एक ही cell में City, Area और Store ID हो तो filter या lookup नहीं हो पाता. तीन columns हों तो हो जाता है.
छोटा उदाहरण: "Pune, Kothrud, BLK-PUN-01" यह Pune | Kothrud | BLK-PUN-01 में spill होता है.
First look at the data (before → after):
Expected result — After TEXTSPLIT + TRIM
| B: City | C: Area | D: Store ID |
|---|---|---|
| Pune | Kothrud | BLK-PUN-01 |
| Nashik | College Road | BLK-NSK-01 |
| Nagpur | Dharampeth | AMN-NGP-01 |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Vikram Rao) are made up for this lesson on splitting columns.
Before table and formula as text
Before — One column holds three facts
| A: Location |
|---|
| Pune, Kothrud, BLK-PUN-01 |
| Nashik, College Road, BLK-NSK-01 |
| Nagpur, Dharampeth, AMN-NGP-01 |
About this data: Demo table only — not real customer or company data. Built to practise splitting columns.
Formula / click path
B2: =TRIM(TEXTSPLIT(A2,",")) → spills into B2:D2
Do it step by step:
- Click B2. Keep C2:D2 empty so the result can spill.
- Type
=TRIM(TEXTSPLIT(A2,","))and press Enter. - Copy B2 down to the other rows.
- If you see #SPILL!, clear the cells to the right.
- No Microsoft 365? Use Data › Text to Columns › Delimited › Comma instead.
स्टेप बाय स्टेप करून बघा:
- B2 वर click करा. Result spill व्हायला C2:D2 रिकामे ठेवा.
=TRIM(TEXTSPLIT(A2,","))type करा आणि Enter दाबा.- B2 खालच्या rows मध्ये copy करा.
-
SPILL! दिसलं तर उजवीकडच्या cells रिकाम्या करा.
- Microsoft 365 नाही? त्याऐवजी Data › Text to Columns › Delimited › Comma वापरा.
स्टेप बाय स्टेप करके देखें:
- B2 पर click करें. Result spill होने के लिए C2:D2 खाली रखें.
=TRIM(TEXTSPLIT(A2,","))type करें और Enter दबाएँ.- B2 को नीचे की rows में copy करें.
-
SPILL! दिखे तो दाईं ओर की cells खाली करें.
- Microsoft 365 नहीं है? उसकी जगह Data › Text to Columns › Delimited › Comma इस्तेमाल करें.
Before
| Location |
|---|
| Kothrud, Pune, 411038 |
| College Road, Nashik, 422005 |
| Dharampeth, Nagpur, 440010 |
About this data: Made-up rows so you can copy the pattern for splitting columns. Currencies may use ₹, £, € or $.
After
| Area | City | Pincode |
|---|---|---|
| Kothrud | Pune | 411038 |
| College Road | Nashik | 422005 |
| Dharampeth | Nagpur | 440010 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
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.
Loading practice tasks...
- Total parcels (Easy): Add up the "Parcels" column. (sheet "Data")
- Average cost (Easy): Average of the Cost (£) column, rounded to 2 decimals. (sheet "Data")
- Rows in Royal Mail (Easy): How many rows have Carrier "Royal Mail"? (sheet "Data")
- Largest parcels (Easy): The largest value in the "Parcels" column. (sheet "Data")
- Clean name (Easy): Trim the "Name" value in the first row. (sheet "Text")
- Proper case (Easy): the "Name" value in the first row trimmed and in proper case. (sheet "Text")
- Code prefix (Easy): First 3 characters of the "Carrier" value in the first row. (sheet "Text")
- Code number (Easy): Last 4 characters of the "Carrier" value in row 3. (sheet "Text")
- Parcels for Royal Mail (Medium): Total of the Parcels column where Carrier is "Royal Mail". (sheet "Data")
- Royal Mail in Glasgow (Medium): Total of the Parcels column where Carrier is "Royal Mail" and City is "Glasgow". (sheet "Data")
- City part (Medium): Characters 5 to 7 of the "Carrier" value in row 4. (sheet "Text")
- Name length (Medium): Length of the "Name" value in row 3. (sheet "Text")
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
