📎 This article includes 1 downloadable practice file ↓
Pulling pieces out of text used to mean nesting LEFT, MID and FIND. Excel 365 now has three functions that read like English.
In this article
TEXTBEFORE and TEXTAFTER
=TEXTBEFORE(A2, "@") ' [email protected] -> asha.verma
=TEXTAFTER(A2, "@") ' -> company.in
=TEXTAFTER(A2, " ", -1) ' last word (surname)
=TEXTBEFORE(A2, ",", , , , A2) ' text before the comma, or all of it if noneA negative instance number counts from the end — perfect for “last word” or file extensions (=TEXTAFTER(A2,".",-1)).
TEXTSPLIT
=TEXTSPLIT(A2, ",") ' "Delhi,Mumbai,Pune" across three cells
=TEXTSPLIT(A2, , ",") ' ...down three rows instead
=TEXTSPLIT(A2, {",",";"}) ' split on comma OR semicolon
=TRIM(TEXTSPLIT(A2, ",")) ' remove spaces around each itemOld way vs new way
| Task | Before | Now |
|---|---|---|
| Domain from email | =MID(A2,FIND("@",A2)+1,99) |
=TEXTAFTER(A2,"@") |
| First name | =LEFT(A2,FIND(" ",A2)-1) |
=TEXTBEFORE(A2," ") |
💡 Still sharing files with Excel 2019 users? These functions show
#NAME? for them. Use Flash Fill or the old formulas in shared workbooks.📎 Practice files for this article
- 📗Text-cleaning scenarios workbookExtract and clean text (GSTIN, PAN, emails, names) with PASS/FAIL checks, plus the 3 mistakes that break text formulas.⬇ XLSX · 8 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong