Copy a column of email addresses out of a signup form and half of them come back with the display name still glued on: Sarah Chen <[email protected]>. Or export a URL list and every row is https://example.com/blog/2024/how-to when what you actually want is the slug at the end. The old fix is a three-step tower of LEFT, FIND, and length math, and the moment the input drifts it breaks. TEXTBEFORE and TEXTAFTER are Excel’s answer to that pattern. Two functions, one call each, and the position math disappears.
Why LEFT and FIND stop being enough
The pre-2022 pattern is muscle memory for a lot of us: find the delimiter position, subtract, feed the number into LEFT or MID. It works right up until a row is missing the delimiter, and then FIND returns #VALUE!, LEFT propagates it, and the whole column lights up red. You wrap the thing in IFERROR, and now the error handler is longer than the formula.
=IFERROR(LEFT(A2,FIND("@",A2)-1),A2)Two functions, one guard, and it still miscounts if the address has a display name in front.
=TEXTBEFORE(A2,"@",,,,A2)One function. The last argument returns the original text if the delimiter is missing.
The rewrite is not just shorter. Because the delimiter is named directly, a teammate reading the formula six months later can see the intent without translating FIND-1. If you already switched to modern text tools like splitting one cell into columns with TEXTSPLIT, this is the same idea for the single-slice case.
The two arguments that do most of the work
Both functions have the same shape: give them the cell, then the delimiter. Everything else is optional. In practice you set the first two, ignore the rest, and only reach for the extra arguments when the data pushes back.
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])text is the string to slice — a cell reference or a literal. delimiter is what marks the cut, and it can be one character, a whole word, or an array of options in curly braces. That’s it for required arguments. According to the Microsoft Support reference for TEXTAFTER, both were added to Microsoft 365 and shipped in Excel 2024; Excel 2019 and 2021 do not have them.
| Argument | Default | When you need it |
|---|---|---|
instance_num |
1 | Pick the 2nd, 3rd, or last occurrence of the delimiter. |
match_mode |
0 (case-sensitive) | Set to 1 to ignore case when the delimiter mixes upper/lower. |
match_end |
0 | Set to 1 to treat the end of the string as an implicit delimiter. |
if_not_found |
#N/A | Return the original text, an empty string, or a flag when the delimiter is missing. |
Picking the right slice with instance_num
Real data rarely has one clean split. A URL has three or four slashes, a log line has a dozen spaces, a product SKU is a chain of hyphens. instance_num tells the function which delimiter to cut at. A positive number counts from the left; a negative number counts from the right, which is the trick that replaces the old “reverse the string, find the last space” hack.
| A | B | C | |
|---|---|---|---|
| 1 | URL | Formula | Result |
| 2 | https://acme.com/blog/2024/how-to | =TEXTAFTER(A2,”/”,-1) | how-to |
| 3 | https://acme.com/blog/2024/how-to | =TEXTAFTER(A3,”/”,3) | 2024/how-to |
| 4 | SKU-BLK-XL-2024 | =TEXTBEFORE(A4,”-“,-1) | SKU-BLK-XL |
Row 2 grabs the last URL segment without ever counting slashes. Row 3 keeps everything after the third slash, which is how you’d pull an entire path once the domain is stripped. Row 4 removes the year suffix from a SKU by cutting at the last hyphen — useful when you want to group last season’s variants under one code.
Passing multiple delimiter options
When the same field arrives with either a comma or a semicolon, wrap both in curly braces. =TEXTBEFORE(A2,{",",";"}) stops at whichever appears first. This is one of the few times the array syntax is worth the extra characters — the alternative is nested IFERROR calls, which read worse than they run.
Extract what sits between two delimiters
The most common real-world extract is not before or after a delimiter — it’s the piece between two of them. The domain in an email address. The middle segment in a versioned filename. The city name between two commas in a mailing address. Nest TEXTBEFORE inside TEXTAFTER: first cut off the front, then cut off the back.
=TEXTBEFORE(TEXTAFTER(A2,"@"),".")Read from the inside out. TEXTAFTER(A2,"@") drops the local-part and returns everything after the @. TEXTBEFORE(..., ".") then trims from the first dot onward. For [email protected] the result is acme — the bare company name, useful for matching against a customer table.
if_not_found: =TEXTBEFORE(TEXTAFTER(A2,"@",,,,""),".",,,,""). A missing @ now yields an empty string instead of cascading a #N/A up through the outer function.
The same pattern picks a middle path segment out of a URL — =TEXTBEFORE(TEXTAFTER(A2,"/",3),"/") reads from after the third slash up to the next one. Once you internalize the “peel outer, peel outer, keep the middle” shape, most one-off extract tasks collapse to a single nested formula.
Spill one formula down a whole column
Both functions accept a range as the first argument and spill the result down. Instead of dragging a formula to fill 5,000 rows, you write it once and Excel fans it out. This is the piece the vendor docs mention in passing and never demonstrate.
| A | B | |
|---|---|---|
| 1 | Signup line | Email only |
| 2 | Sarah Chen <[email protected]> | =TEXTBEFORE(TEXTAFTER(A2:A100,”<“),”>”) |
| 3 | Ben Ito <[email protected]> | ↓ spills |
| 4 | Rita Vora <[email protected]> | ↓ spills |
Type the formula in B2 once. It reads the whole range A2:A100, applies the extract to each row, and spills down to B100. Delete B2 and the spill disappears with it — one anchor cell, no drag, no fill-handle inconsistencies. If a spilled result runs into an existing value below, you’ll see #SPILL!; the fix is usually to clear the blocking cells, and we cover the full walkthrough in how to fix the Excel #SPILL! error.
When the delimiter is missing
The default behavior on a missing delimiter is #N/A. That’s fine when a red cell is exactly the signal you want. It’s the wrong default when you’re building a report for someone who reads the sheet and thinks the formula is broken. Two arguments control the fallback: match_end and if_not_found.
match_end=1 silently changes the answer, not just the error. =TEXTBEFORE("acme","/",,,1) returns the entire acme because the end of the string counts as the delimiter. If you use it, add a note next to the formula so a future editor knows why every row without the delimiter looks like a valid extract.
=TEXTAFTER(A2,"@",,,,"no email") // return a labelled string
=TEXTBEFORE(A2,",",,,1) // treat end of string as a delimiter
=IFERROR(TEXTAFTER(A2,"@"),A2) // fallback: keep the originalThe third line is a hedge worth remembering. If the source data is dirty enough that some rows legitimately have no delimiter, echoing the original text back is often the honest answer. It also keeps the sheet readable during review — a mixed column of extracted values and raw strings is easier to spot-check than one that’s half #N/A. For heavier text-cleaning jobs where TEXTBEFORE isn’t enough, Excel’s Flash Fill for pattern extraction is the no-formula fallback.
Where to start
Pick one column in a sheet you actually work with — an email list, a URL export, a product code column — and rewrite the extract as a single spilled TEXTBEFORE or TEXTAFTER. Add an if_not_found that echoes the original. That one substitution replaces a fragile LEFT+FIND+IFERROR tower and leaves the intent readable at a glance.
- ✓ You’re on Microsoft 365 or Excel 2024 — older versions won’t have the function
- ✓ The delimiter is a fixed string, not a wildcard pattern
- ✓ You’ve decided what a missing delimiter should return:
#N/A, an empty string, or the original text - ✓ The source range fits in one column so the formula can spill without a
#SPILL!
The rest — nesting, match modes, multi-delimiter arrays — comes up when the data gets weirder, and by then the shape will feel familiar.