3.9 TEXTBEFORE, TEXTAFTER and TEXTSPLIT
Microsoft 365 / Excel 2024 (not in Excel 2021 or older).
| Function | Syntax (main arguments) | Example on PUN-Kothrud-01 |
Result |
|---|---|---|---|
| TEXTBEFORE | TEXTBEFORE(text, delimiter, [instance_num], …) |
=TEXTBEFORE(A2,"-") |
PUN |
| TEXTAFTER | TEXTAFTER(text, delimiter, [instance_num], …) |
=TEXTAFTER(A2,"-",-1) |
01 |
| TEXTSPLIT | TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], …) |
=TEXTSPLIT(A2,"-") |
PUN · Kothrud · 01 (spills into 3 cells) |
About this data: Teaching sample for a fictional shop “GreenField Dairy”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on textbefore textafter and textsplit.
A negative instance_num counts from the end, so TEXTAFTER(A2,"-",-1) returns the text after the last hyphen.
Worked example – e-mail parts. zoya@example.com: =TEXTBEFORE(A2,"@") → zoya; =TEXTAFTER(A2,"@") → example.com. Full name split: =TEXTSPLIT("Ravindra Bagale"," ") → Ravindra | Bagale.
Ravindra Bagale's Tip
The result of TEXTSPLIT spills (Module 9) – if the cells to the right are not empty, you get #SPILL!. Many students forget this. Keep the space to the right empty before entering the formula. And if your office has Excel 2016/2019, these functions won't work – use FIND + MID there.
Ravindra Bagale's Tip – मराठी
TEXTSPLIT चा result spill होतो (Module 9) – उजवीकडच्या cells रिकाम्या नसतील तर #SPILL! येतो. बरेच students हे विसरतात. Formula लावण्याआधी उजवीकडे जागा रिकामी ठेवा. आणि office मध्ये Excel 2016/2019 असेल तर ही functions चालणार नाहीत – तिथे FIND + MID वापरा.
Ravindra Bagale's Tip – हिंदी
TEXTSPLIT का result spill होता है (Module 9) – दाईं तरफ़ की cells खाली न हों तो #SPILL! आता है. बहुत से students यह भूल जाते हैं. Formula लगाने से पहले दाईं तरफ़ जगह खाली रखो. और office में Excel 2016/2019 हो तो ये functions नहीं चलेंगे – वहाँ FIND + MID इस्तेमाल करो.
Practice task
Split Kothrud, Pune, 411038 into three columns with TEXTSPLIT. Extract the domain from ten e-mail addresses with TEXTAFTER.
Loading practice tasks...
- City code (Easy): Get the text before "-" in A2.
- Email user name (Easy): Get the text before "@" in A3.
- UK shop: country code (Easy): Get the text before "-" in Shop!A2.
- UK shop: email domain (Easy): Get the text after "@" in Shop!A3.
- Email domain (Medium): Get the text after "@" in A3.
- First name (Medium): Get the first name from A4.
- UK shop: shoe size (Medium): Get the text after the last "-" in Shop!A2.
- Store number (Hard): Get the text after the last "-" in A2.
- Area name (Hard): Get Kothrud from A2.
- UK shop: product (Hard): Get SHOE from Shop!A2.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
