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
TEXTSPLIT cha result spill hoto (Module 9) – ujvikadchya cells rikamya nastil tar #SPILL! yeto. Khup students he visartat. Formula lavaychya aadhi ujvikade jaga rikami theva. Ani office madhe Excel 2016/2019 asel tar he functions chalnar nahit – tithe FIND + MID vapra.
Practice task
Split Kothrud, Pune, 411038 into three columns with TEXTSPLIT. Extract the domain from ten e-mail addresses with TEXTAFTER.