The Excel TEXTBEFORE function returns everything in a text value that comes before a delimiter — the username before the @, the area code before the dash, the folder before the slash. It replaces the old LEFT(A2, FIND("@",A2)-1) contraption with one readable argument list, and it can count delimiters from the end of the text, which FIND never could. Its twin is TEXTAFTER, and for splitting into every piece at once, use TEXTSPLIT.
=TEXTBEFORE(A2, "-", 2) stops at the second dash, and -1 counts from the end.
Syntax
| Argument | Description | |
|---|---|---|
text | Required | The text to search in. |
delimiter | Required | The character(s) to look for. Can be more than one character. |
instance_num | Optional | Which occurrence to stop at. Default 1. Negative counts from the end: -1 means the last occurrence. |
match_mode | Optional | 0 (default) = case-sensitive · 1 = case-insensitive. |
match_end | Optional | 1 treats the very end of the text as a delimiter, so a missing delimiter returns the whole text instead of an error. Default 0. |
if_not_found | Optional | What to return when the delimiter isn’t found — without it you get #N/A. |
Available in: Excel for Microsoft 365 and Excel for the web only. Excel 2021 and earlier show #NAME? — there the equivalent is LEFT(A2, FIND("@",A2)-1).
Grab the text before a delimiter
Emails sit in column A and you need the usernames. One short formula, no FIND arithmetic:
| A | C | ||
|---|---|---|---|
| 1 | Username | ||
| 2 | jane.doe@dfwexcel.com | jane.doe | |
| 3 | m.alvarez@example.org | m.alvarez | |
| 4 | kim@plano.k12.tx.us | kim |
The delimiter can be longer than one character — useful for splitting on words:
And instance_num picks which occurrence matters when the delimiter repeats:
Try it: interactive TEXTBEFORE demo
Edit the text, pick a delimiter and an instance number — including negatives, which count from the end.
Negative instances and power moves
Negative instance_num is the headline feature — counting from the end used to take a SUBSTITUTE/FIND tangle. Get the folder path from a full file path by stopping at the last slash:
Strip a file extension the same way — everything before the last dot:
Pair it with TEXTAFTER to slice out the middle of a string — the domain between @ and the final dot:
And make missing delimiters harmless with if_not_found — far cleaner than wrapping in IFERROR:
Errors & common pitfalls
#N/A — delimiter not found. The delimiter (or the nth instance you asked for) doesn’t exist in the text. Supply if_not_found for a graceful fallback, or set match_end to 1 to have the whole text returned instead.
#VALUE! — instance_num is 0 or out of range. Zero is never valid, and an instance number larger than the text could possibly hold also fails.
#NAME? — older Excel. TEXTBEFORE exists only in Excel for Microsoft 365 and Excel for the web. Elsewhere, use =LEFT(A2, FIND("@",A2)-1).
Pitfall: matching is case-sensitive by default. Searching for "x" ignores "X" until you set match_mode to 1.
Pitfall: the delimiter is not included. TEXTBEFORE stops before the delimiter — no trailing @ or slash in the result. If you expected it, concatenate it back on.
Practice workbook
Frequently asked questions
What's the difference between TEXTBEFORE and TEXTAFTER?
How do I get the text before the LAST occurrence of a character?
=TEXTBEFORE(A2, "/", -1) stops at the last slash. That one argument replaces the old SUBSTITUTE-and-FIND workaround entirely.How do I avoid #N/A when the delimiter might be missing?
=TEXTBEFORE(A2, "@", , , , "none") — or set match_end to 1, which treats the end of the text as a delimiter and returns the whole string.What's the TEXTBEFORE equivalent for older Excel versions?
=LEFT(A2, FIND("@", A2) - 1). It handles only the first occurrence and errors when the delimiter is missing, so wrap it in IFERROR for safety.Can the delimiter be more than one character?
=TEXTBEFORE(A2, " - ") splits at a spaced dash, and an array of delimiters like {",",";"} matches whichever comes first.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class