The TEXTAFTER and TEXTBEFORE functions in Excel
TEXTAFTER returns everything after a delimiter; TEXTBEFORE everything before it. The domain of an e-mail address, a first name, the last part of a code: one short formula, no FIND and no counting.
Both are available in Excel 365 and Excel for the web. In older versions, use LEFT, MID and FIND instead.
Syntax
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
| Argument | What it means |
|---|---|
| text | The text or cell to split. |
| delimiter | The character or text to split at, e.g. "@". |
| instance_numoptional | Which occurrence to use. Default 1; -1 means the last one. |
| match_modeoptional | 0 = case-sensitive (default), 1 = ignore case. |
| match_endoptional | 1 treats the end of the text as a delimiter. Rarely needed. |
| if_not_foundoptional | What to return if the delimiter isn't there, instead of #N/A. |
Examples
1. Everything after the @
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Domain | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | shopmail.com |
Compare with =MID(B2,FIND("@",B2)+1,100): same result, far easier to read.
2. Everything before: TEXTBEFORE
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | User name | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | omar.haddad |
The part of the address before the @.
3. After the second dash
| A | B | C | D | |
|---|---|---|---|---|
| 1 | SKU | Color | ||
| 2 | SH-4417-BLU | omar.haddad@shopmail.com | BLU |
instance_num 2 skips the first dash. =TEXTAFTER(A2,"-",-1) (after the last dash) gives the same here.
Common mistakes
#NAME? error. The Excel version doesn't have these functions (Excel 2021 and older).
Fix: Use LEFT, MID and FIND for files that must open in older versions.
#N/A when the delimiter is missing, e.g. a name without a space.
Fix: Use the if_not_found argument:
=TEXTBEFORE(A2," ",1,0,0,A2)returns the whole name when there's no space.
Frequently asked questions
How do I split a text into several columns at once?
TEXTSPLIT (also Excel 365) splits at every delimiter and spills the parts into neighboring cells.