TEXTBEFORE/TEXTAFTER vs LEFT/MID/RIGHT: delimiter or position
Every 'extract part of a cell' task used to mean LEFT/MID/RIGHT wrapped around FIND arithmetic. TEXTBEFORE and TEXTAFTER say what you mean — 'the part before the dash' — but they're new enough that portability is the real decision.
The differences at a glance
| TEXTBEFORE | TEXTAFTER | LEFT | MID | RIGHT | FIND | |
|---|---|---|---|---|---|---|
| Cut at a delimiter | One readable call: TEXTBEFORE(A2,"-") | LEFT(A2,FIND("-",A2)-1) — the -1 is on you | ||||
| Nth or LAST occurrence | Built-in argument (negative counts from the end) | SUBSTITUTE/CHAR trickery | ||||
| Delimiter missing | Optional if_not_found argument | FIND throws #VALUE!; wrap in IFERROR | ||||
| Fixed-position slicing (chars 3-6) | Wrong tool | Exactly what MID is for | ||||
| Availability | Excel 365 (documented) & Excel for the web (executed); LibreOffice 25.8+ (executed, with quirk flags — see their pages). NOT in Google Sheets (executed: #NAME?) | Universal, verified everywhere |
Which should you use?
- TEXTBEFORE/TEXTAFTER — Delimiter-based extraction on modern versions — clearer intent, saner edge cases, and chainable: TEXTBEFORE(TEXTAFTER(A2,"("),")") reads like the task.
- LEFT/MID/RIGHT — Position-based slicing (fixed-width codes), or any file that must open in Google Sheets or older Excel/LibreOffice 24.2 — the classic patterns run everywhere and always will.
Compatibility (Excel for the web, Sheets & LibreOffice executed; desktop Excel per docs)
LEFT/MID/RIGHT/FIND execute identically in every LibreOffice version we test; Google Sheets matches in our executed run; desktop Excel behavior matches per Microsoft's documentation — we do not run desktop Excel. TEXTBEFORE/TEXTAFTER first appear in LibreOffice 25.8 (executed: #NAME? in 24.2, 24.8 and 25.2) and are executed in Excel for the web; they are absent from Google Sheets (executed: #NAME? on 2026-08-29 — use REGEXEXTRACT, or MID+FIND, there). Where the modern functions do run they carry verified quirk flags — our executed cases found edge-case behavior differences vs Excel (see each function's page for the failing case), so test delimiter-missing paths when a sheet must round-trip. Excel for the web is a separate application with its own calculation engine, and that one we do execute: all 25 corpus cases for these functions matched the documented values there (recalculated on OneDrive, 2026-09-01); the per-case values are on the individual function pages.
Example formulas
| Modern: domain from email | =TEXTAFTER(A2,"@") |
| Classic equivalent | =MID(A2,FIND("@",A2)+1,100) |
| Between parentheses, chained | =TEXTBEFORE(TEXTAFTER(A2,"("),")") |
Full per-version details on each function page: TEXTBEFORE · TEXTAFTER · LEFT · MID · RIGHT · FIND.