TEXTAFTER
Return the text that comes after a chosen character or word. Great for splitting strings without nesting MID, FIND, and LEN.
Spreadsheet editor
Spreadsheet editor
Syntax
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
Returns: Text Arguments
| Argument | Required | Description |
|---|---|---|
| text | Yes | The text you want to search inside. This can be typed in quotation marks or a reference to a cell that holds the text. |
| delimiter | Yes | The character or word that marks where to split. TEXTAFTER returns everything that comes after it. |
| instance_num | No | Which occurrence of the delimiter to split on. Defaults to 1 (the first). Use a negative number to count from the end, so -1 finds the last one. It cannot be 0. |
| match_mode | No | Set to 0 (the default) for a case-sensitive match, or 1 to ignore case. |
| match_end | No | Set to 1 to treat the very end of the text as an extra delimiter. This only matters in an edge case: if you ask for an instance of the delimiter that doesn't exist (like the 2nd dash when there's only one), TEXTAFTER uses the end of the text as that delimiter and returns an empty string instead of a #N/A error. Defaults to 0. |
| if_not_found | No | The value to return when the delimiter isn't found. By default TEXTAFTER returns a #N/A error. |
About
TEXTAFTER pulls out everything that comes after a delimiter you pick, such as a comma, a space, or a whole word. You give it the text, tell it what to look for, and it hands back the part that follows. It saves you from the old habit of stacking MID, FIND, and LEN together just to grab the tail end of a string.
Use it when you need the second half of a value: the domain after the @ in an email, the city after a comma in an address, or the file name after the last slash in a path. The optional instance number lets you choose which delimiter to split on, and a negative number counts from the end, so grabbing text after the last occurrence is easy. You can also make the match case-insensitive or treat the end of the text as a delimiter.
TEXTAFTER works in Microsoft 365 and Excel 2024. On older versions you can recreate it by combining MID with FIND, or SEARCH when you need a case-insensitive match. To grab the part before the delimiter instead, use RIGHT and LEFT for fixed positions, or clean up the result with TRIM.
Use it when you need the second half of a value: the domain after the @ in an email, the city after a comma in an address, or the file name after the last slash in a path. The optional instance number lets you choose which delimiter to split on, and a negative number counts from the end, so grabbing text after the last occurrence is easy. You can also make the match case-insensitive or treat the end of the text as a delimiter.
TEXTAFTER works in Microsoft 365 and Excel 2024. On older versions you can recreate it by combining MID with FIND, or SEARCH when you need a case-insensitive match. To grab the part before the delimiter instead, use RIGHT and LEFT for fixed positions, or clean up the result with TRIM.
Examples
Grab the file name after the last slash
See how -1 finds the last slash in a file path, so you always get the file name. Add or remove folders in column A and watch the result follow the final slash.
Spreadsheet editor
Avoid #N/A when the delimiter is missing
Watch what happens when a product code has no dash. The plain formula returns #N/A, but the if_not_found argument falls back to the original code. Edit a code to remove its dash and compare the two columns.
Spreadsheet editor
Split a location into city and country
Pair TEXTAFTER with TEXTBEFORE to split one column into two. Try editing a location and see the city and country update side by side.
Spreadsheet editor
Watch out for
⚠
#N/A when the delimiter isn't there
If the delimiter never appears in the text, TEXTAFTER returns a #N/A error by default, which can break the rest of your sheet.
→ Set the if_not_found argument to a fallback value, like =TEXTAFTER(A2, "@", 1, 0, 0, "") to return a blank instead. You can also wrap it in IFERROR.
⚠
Case-sensitive matching by surprise
By default the match is case-sensitive, so splitting on "x" will skip an uppercase "X" and may miss the spot you wanted.
→ Set match_mode to 1 to ignore case, like =TEXTAFTER(A2, "x", 1, 1).
⚠
A leading space sneaks into the result
Splitting "London, UK" on just "," leaves the result starting with a space.
→ Include the space in the delimiter (", ") or clean the result with TRIM.
Tips & notes
Use a negative instance_num to work from the right side of the text, which is the easiest way to split on the last occurrence of a delimiter. TEXTAFTER does not accept wildcards in the delimiter. If a stray space ends up in the result, wrap it in TRIM, and use IFERROR to keep a missing delimiter from breaking the rest of your sheet.
Common questions
How do I get the text after the last occurrence of a character?
Use a negative instance number. =TEXTAFTER(A2, "/", -1) returns the text after the final slash, which is handy for file paths and URLs.
How is TEXTAFTER different from MID?
MID pulls characters from a fixed position you supply. TEXTAFTER finds the position for you by searching for a delimiter, so you don't have to calculate where to start.
Why does TEXTAFTER return #N/A?
It can't find the delimiter in your text. Add the if_not_found argument to return a fallback value, or check that the delimiter actually appears and matches the right case.