TEXTBEFORE is the modern replacement for LEFT+FIND combinations. Here's when it's better and when LEFT still wins.
Before TEXTBEFORE, extracting text before a specific character required nesting LEFT and FIND:
=LEFT(A2, FIND(" ", A2) - 1)
-- Extract first name from "Sarah Johnson"
-- Works but: crashes with #VALUE! if no space exists
-- Hard to read, hard to maintain
=LEFT(A2, FIND("@", A2) - 1)
-- Extract username from email
-- Same problem — crashes if no @ found=TEXTBEFORE(A2, " ")
-- Extract first name from "Sarah Johnson"
-- Cleaner, handles errors gracefully via if_not_found argument
=TEXTBEFORE(A2, "@")
-- Extract username from email
-- No FIND needed, no -1 adjustment=TEXTBEFORE(A2, ".", -1) — impossible cleanly with LEFT=TEXTBEFORE(A2, "X", 1, 1)=TEXTBEFORE(A2, "@", 1, 0, 0, "No @ found")=LEFT(A2, 3) — extract first 3 characters, no delimiter needed| Task | LEFT approach | TEXTBEFORE approach |
|---|---|---|
| First 3 characters | =LEFT(A2,3) ✓ simpler | Not suitable |
| Text before first space | =LEFT(A2,FIND(" ",A2)-1) | =TEXTBEFORE(A2," ") ✓ simpler |
| Text before last dot | Complex nested formula | =TEXTBEFORE(A2,".",-1) ✓ much simpler |
| Works in Excel 2019 | ✓ Yes | ✗ No |
Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.
Start the Excel Basics track free →