Excel TEXTBEFORE and TEXTAFTER: Split Text Without Counting Characters
Return the part of a text value before or after a delimiter you name.
These two replace the most tedious formula in Excel. Extracting an email domain used to mean =RIGHT(A2, LEN(A2) - FIND("@", A2)) — three functions and an off-by-one you get wrong at least once. Now it is =TEXTAFTER(A2, "@").
You name the delimiter and get back everything on one side of it. TEXTBEFORE takes the left part, TEXTAFTER takes the right, and neither asks you to count a single character. Where TEXTSPLIT gives you all the pieces at once, these give you the one piece you actually wanted.
The third argument is where they get properly useful. It says which occurrence of the delimiter to cut at, and it accepts negative numbers to count from the end: -1 means the last one. That is how you take a file extension, or a surname from a name that might have a middle name in it.
Syntax
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])Arguments
textRequired- The value to cut.
delimiterRequired- What to cut at. An array constant cuts at any of several delimiters.
instance_numOptional- Which occurrence to use. 1 is the first (the default); -1 is the last, -2 the second from the end.
match_modeOptional- 0 is case sensitive (the default), 1 is not.
if_not_foundOptional- What to return when the delimiter is absent. Without it you get #N/A, which is the most common surprise these functions produce.
The example data
Headers in row 1, data in A2:C6.
| A | B | C | |
|---|---|---|---|
| 1 | Player | Score | |
| 2 | Alice Moreau | 1240 | alice@northwind.com |
| 3 | Bruno Santos | 385 | bruno@southgate.co.uk |
| 4 | Chen Wei | 2100 | chen@northwind.com |
| 5 | Dana Okafor | 940 | dana@eastvale.org |
| 6 | Erik Halls | 1560 | erik@southgate.co.uk |
Worked examples
=TEXTAFTER(C2, "@")Result: northwind.com
The email domain in one step. Compare with the RIGHT/LEN/FIND version this replaces.
=TEXTBEFORE(C2, "@")Result: alice
The other half. No -1 needed, because you are naming the delimiter, not a position.
=TEXTAFTER(A2, " ", -1)Result: Moreau
The last space rather than the first, so this returns the surname even when a middle name is present.
=TEXTAFTER(A2, "@", 1, 0, 0, "No domain")Result: No domain
There is no @ in a name. The last argument turns the #N/A into something readable.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#N/A
Why it happens: The delimiter does not appear in the text, and no if_not_found was supplied.
How to fix it: Supply the sixth argument. This is by far the most common problem with these functions, and it appears the moment one row in a thousand is formatted differently.
#VALUE!
Why it happens: instance_num is 0, or is larger than the number of times the delimiter occurs.
How to fix it: Occurrences count from 1, not 0. Use -1 for the last rather than guessing how many there are.
Result has a leading space
Why it happens: Cutting on "," rather than ", " leaves the space that followed the comma attached to the result.
How to fix it: Either include the space in the delimiter, or wrap the result in TRIM.
#NAME?
Why it happens: These shipped in 2022, later than FILTER and UNIQUE, so an Excel that has those may still not have these.
How to fix it: Needs current Microsoft 365. Elsewhere use LEFT/RIGHT with FIND.
Tips worth knowing
- -1 as instance_num is the argument worth remembering: last space for a surname, last dot for a file extension, last slash for a filename.
- Pass an array constant to cut at any of several delimiters: TEXTBEFORE(A2, {",", ";"}).
- Nest them to take a middle section: TEXTBEFORE(TEXTAFTER(A2, "-"), "-") takes what is between the first two hyphens.
- Use TEXTSPLIT instead when you want every piece; these are for when you want exactly one.
Frequently asked questions
How do I extract the domain from an email address?
=TEXTAFTER(A2, "@") returns everything after the @. Add a fallback for rows without one: =TEXTAFTER(A2, "@", 1, 0, 0, ""). Before these functions existed this needed RIGHT, LEN and FIND together.
How do I get the last word of a cell?
=TEXTAFTER(A2, " ", -1). The -1 counts occurrences from the end, so it cuts at the final space regardless of how many words there are.
What is the difference from TEXTSPLIT?
TEXTSPLIT breaks the text into every piece and spills them across cells. TEXTBEFORE and TEXTAFTER return exactly one piece into one cell. If you want a first name and nothing else, these keep the result contained; if you want the whole record broken up, TEXTSPLIT does it in one go.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.