TEXTBEFORE and TEXTAFTER in Excel
TEXTBEFORE(text, delimiter, [instance_num]) returns everything before a delimiter and TEXTAFTER(text, delimiter, [instance_num]) returns everything after it. A negative instance number counts from the end, so -1 means the last occurrence. They replace LEFT, RIGHT and MID combined with FIND.
Syntax
TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])| Part | What it is |
|---|---|
text | The text to take a piece from. |
delimiter | The character or text that marks the split, such as a space, @ or a comma. |
instance_num | Which occurrence to split at. 1 is the first (default), -1 is the last. |
match_mode | 0 case-sensitive (default), 1 ignores case. |
match_end | 1 treats the end of the text as a delimiter. Default 0. |
if_not_found | What to return when the delimiter is not in the text. Default is #N/A. |
Examples
=TEXTAFTER("ana@example.com", "@")Result: example.com
=TEXTBEFORE("Lopez, Maria", ",")Result: Lopez
=TEXTAFTER("report.final.xlsx", ".", -1)Result: xlsx
-1 splits at the last dot, which is how to get a file extension.
=TEXTBEFORE(A2, " ", 1, 0, 0, A2)Result: The first word, or the whole text when there is no space
The sixth argument avoids #N/A for one-word cells.
Common mistakes
- #N/A means the delimiter is not in the text. Give the sixth argument a fallback.
- #NAME? means the Excel version does not have these functions. They are in Microsoft 365 and Excel 2024, not Excel 2021 or 2019. Use LEFT, MID and FIND there.
- The first dot is used when the last one was wanted. Pass -1 as the instance number.
- A comma followed by a space leaves a leading space in the result. Use ", " as the delimiter or wrap the result in TRIM.
Practice TEXTBEFORE and TEXTAFTER
These drills check your answer on live data, free, no account needed.
- File extensionReturn the file extension: the text after the last dot in each file name. Some names contain more than one dot. Fill B2 and B3.
- Split last name, first nameEach name is written as the last name, a comma, a space, then the first name. Put the last name in column B and the first name in column C for both rows. The names change, so find the comma rather than counting characters.
- Pull out the domainExtract everything after the @ sign from each email address. Fill B2 and B3.
Questions
How do I get the text after the last occurrence of a character?
=TEXTAFTER(A2, "/", -1) returns everything after the last slash. A negative instance number counts from the end.
What can I use instead of TEXTBEFORE in older Excel?
=LEFT(A2, FIND(" ", A2) - 1) returns the text before the first space. For the text after it, =MID(A2, FIND(" ", A2) + 1, LEN(A2)).
Are TEXTBEFORE and TEXTAFTER case-sensitive?
Yes by default. Pass 1 as the fourth argument to ignore case in the delimiter.
Related
- LEN, LEFT, RIGHT, MIDHow to pull characters from the start, end or middle of a cell with LEFT, RIGHT and MID, measure length with LEN, and use FIND to split text at a character.
- SUBSTITUTEHow the Excel SUBSTITUTE function replaces one piece of text with another, with examples for removing dashes, swapping the nth occurrence and counting commas.
- TRIMHow the Excel TRIM function removes leading, trailing and repeated spaces, why lookups fail without it, and how to remove the non-breaking spaces it leaves.
- TEXTJOINHow the Excel TEXTJOIN function joins a range into one string with a separator and skips empty cells, with examples for comma lists and a conditional join.