TEXTAFTER returns the portion of a text string that comes after a specified delimiter. It lets you split names, codes, or addresses without complex formulas.
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])What does TEXTAFTER do?
TEXTAFTER is a Text function that extracts everything after a delimiter in a text string. You give it the text, the delimiter (such as a comma, space, or hyphen), and optionally which occurrence to use, and it returns the remaining characters.
Before TEXTAFTER, splitting text often meant combining LEFT, RIGHT, FIND, and LEN, which was error-prone. TEXTAFTER makes the job simple and readable, so you can clean imported data, separate first and last names, or pull a domain from an email address in one step.
Imagine a list of full names in one column and you need only the surnames. With =TEXTAFTER(A2," ") you get the last name instantly, and you can fill the formula down the column.
When to use TEXTAFTER
- Extracting the last name from a full name separated by a space.
- Getting the domain from an email address after the @ symbol.
- Pulling the file name from a full file path after the last backslash.
- Separating a product code from a description after a hyphen.
- Extracting the city from an address after a comma.
- Isolating a value after a colon in a log or key-value string.
TEXTAFTER syntax
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])| Argument | Required? | What to enter |
|---|---|---|
text | Required | The original text or cell reference you want to search. Example: A2 or "Hello-World". |
delimiter | Required | The character or text that marks where to start extracting. Example: " " for a space or "@" for an at sign. |
instance_num | Optional | Which occurrence of the delimiter to use. Default is 1 (first). Use 2 for the second, -1 for the last from the end. |
match_mode | Optional | 0 (default) means case-sensitive; 1 means case-insensitive. Example: 1 ignores case when matching the delimiter. |
match_end | Optional | 0 (default) treats the end of text as a delimiter; 1 does not. Example: 1 prevents returning the whole text when the delimiter is missing. |
if_not_found | Optional | What to return if the delimiter is not found. Example: "Not found" or "" for blank. |
What it returns: TEXTAFTER returns a text string containing the characters after the specified delimiter. If the delimiter is not found, it returns #N/A unless you supply if_not_found.
TEXTAFTER examples
1 Extract the last name from a full name
The goal is to get the surname from a full name where first and last names are separated by a space. The formula =TEXTAFTER(A2," ") looks for the first space in cell A2 and returns everything after it. For "John Smith", it returns "Smith". Fill the formula down to apply it to all names.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Full Name | ||||
| 2 | John Smith | Smith | |||
| 3 | Mary Johnson | ||||
| 4 | David Brown | ||||
| 5 | Sarah Davis | ||||
| 6 | James Wilson | ||||
| 7 |
2 Get the domain from an email address
Here you want the part after the @ sign in each email. =TEXTAFTER(A2,"@") finds the @ and returns the domain. For "alice@example.com", it returns "example.com". This is useful for grouping users by company domain.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | |||||
| 2 | alice@example.com | example.com | |||
| 3 | bob@work.org | ||||
| 4 | carol@site.net | ||||
| 5 | dave@company.co.uk | ||||
| 6 | eve@domain.com | ||||
| 7 |
3 Pull the file name from a full path
You have full file paths and need only the file name. =TEXTAFTER(A2,"\",-1) uses the backslash as the delimiter and -1 to take the last occurrence, so it returns everything after the final backslash. For "C:\Users\Docs\report.xlsx", it returns "report.xlsx". This works even when folder depth varies.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | File Path | ||||
| 2 | C:\Users\Docs\report.xlsx | report.xlsx | |||
| 3 | D:\Projects\Data\sales.csv | ||||
| 4 | E:\Backup\2024\budget.xlsx | ||||
| 5 | F:\Temp\notes.txt | ||||
| 6 | G:\Shared\Team\plan.docx | ||||
| 7 |
4 Separate the city from an address
Addresses often end with a comma and the city. =TEXTAFTER(A2,", ",-1) finds the last comma followed by a space and returns the city. For "123 Main St, Springfield", it returns "Springfield". Using -1 ensures you get the last part even if the street contains commas.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Address | ||||
| 2 | 123 Main St, Springfield | Springfield | |||
| 3 | 456 Oak Ave, Riverside | ||||
| 4 | 789 Pine Rd, Hillview | ||||
| 5 | 101 Elm St, Lakeside | ||||
| 6 | 202 Maple Dr, Brookfield | ||||
| 7 |
5 Extract the value after a colon in a log entry
Log entries like "Error: Timeout" store the message after a colon. =TEXTAFTER(A2,": ") returns the text after the colon and space. For "Error: Timeout", it returns "Timeout". If the delimiter is missing, the formula returns #N/A, so you can add an if_not_found argument like "No message" to handle that gracefully.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Log Entry | ||||
| 2 | Error: Timeout | Timeout | |||
| 3 | Warning: Low disk | ||||
| 4 | Info: Started | ||||
| 5 | Error: NotFound | ||||
| 6 | Warning: High CPU | ||||
| 7 |
Common TEXTAFTER errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | The delimiter was not found in the text and no if_not_found value was provided. | Add the if_not_found argument, for example =TEXTAFTER(A2,"@",1,0,0,"Not found"), or check that the delimiter exists. |
| #VALUE! | The instance_num is 0 or the text argument is not valid text. | Use a non-zero instance_num (1, 2, -1, etc.) and ensure the text is a string or cell reference. |
| #NAME? | The function name is misspelled or your Excel version does not support TEXTAFTER. | Check the spelling and ensure you have Excel for Microsoft 365 or Excel 2024; otherwise use alternative formulas. |
| Empty result | The delimiter is at the end of the text, so there is nothing after it. | Verify the text and delimiter; consider using if_not_found to return a default value. |
Tips for using TEXTAFTER
- Use -1 as instance_num to get text after the last occurrence of a delimiter, which is handy for file paths and addresses.
- Set match_mode to 1 if you want case-insensitive delimiter matching.
- Provide an if_not_found value to avoid #N/A errors and keep reports clean.
- Combine TEXTAFTER with TRIM to remove extra spaces from the result.
- Remember that TEXTAFTER is available only in Excel for Microsoft 365 and Excel 2024; check compatibility before sharing files.
Frequently asked questions
What does TEXTAFTER do in Excel?
TEXTAFTER returns the portion of a text string that comes after a specified delimiter. You can choose which occurrence to use and what to return if the delimiter is missing.
Can I get text after the last delimiter?
Yes. Set the instance_num argument to -1, for example =TEXTAFTER(A2,"\",-1) to get the file name from a path.
Why does TEXTAFTER return #N/A?
It returns #N/A when the delimiter is not found. Supply the if_not_found argument to return a custom value instead.
Is TEXTAFTER case-sensitive?
By default it is case-sensitive. Set match_mode to 1 to make it case-insensitive.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. TEXTAFTER reference