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, Sheets, LibreOffice 24.8+ (with executed QUIRKS — see their pages) | 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 older Excel/LibreOffice 24.2 — the classic patterns run everywhere and always will.
Compatibility (from executed tests)
LEFT/MID/RIGHT/FIND execute identically in every version of all three apps we test. TEXTBEFORE/TEXTAFTER first appear in LibreOffice 24.8 and carry verified quirk flags there — 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.
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.