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) |
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.