Text
Excel 365
◉ 43 views
=TEXTAFTER()
Value returned if no match is found. By default, #N/A is returned.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for Mac
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
Hover argument names in the table for details.
Output typeText
Popularity55 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =TEXTAFTER() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
text |
Text | Required | The text you are searching within. Wildcard characters not allowed. |
delimiter |
Text | Required | The text that marks the point after which you want to extract. |
instance_num |
Number | Optional | The instance of the delimiter after which you want to extract the text. By default, instance_num = 1. A negative number starts searching text from the end. |
match_mode |
Number | Optional | Determines whether the text search is case-sensitive. The default is case-sensitive. Enter one of the following: Values: 0 = Case sensitive.; 1 = Case insensitive.. |
match_end |
Text | Optional | Treats the end of text as a delimiter. By default, the text is an exact match. Enter one of the following: Values: 0 = Don't match the delimiter against the end of the text.; 1 = Match the delimiter against the end of the text.. |
if_not_found |
Number | Optional | Value returned if no match is found. By default, #N/A is returned. |
▣ Examples
▦ Data
| Cell | Value |
|---|---|
A2 |
Little Red Riding Hood's red hood |
A3 |
Little red Riding Hood's red hood |
A4 |
Marcus Aurelius |
A5 |
Socrates |
A6 |
Immanuel Kant |
A7 |
Little Red Riding Hood's red hood |
A8 |
Little red Riding Hood's red hood |
A9 |
Marcus Aurelius |
A10 |
Socrates |
A11 |
Immanuel Kant |
Riding Hood's red hood
=TEXTAFTER(A2,"Red")
#N/A
=TEXTAFTER(A2,"basket")
hood
=TEXTAFTER(A3,"red",2)
Riding Hood's red hood
=TEXTAFTER(A3,"red",-2)
#N/A
=TEXTAFTER(A3,"Red",,FALSE)
#N/A
=TEXTAFTER(A2,"red",3)
Aurelius
=TEXTAFTER(A2," ",,,1)
#N/A
=TEXTAFTER(A3," ",,,0)
Example 9
=TEXTAFTER(A3," ",,,1)
Socrates
=IF(TEXTAFTER(A3," ",,,1)="",A3)
Kant
=TEXTAFTER(A4," ",,,1)
§ Remarks
- When searching with an empty delimiter value, TEXTAFTER matches immediately. It returns the entire text when searching from the front (if instance_num is positive) and empty text when searching from the end (if instance_num is negative).
✦ Tips
- When searching with an empty delimiter value, TEXTAFTER matches immediately. It returns the entire text when searching from the front (if instance_num is positive) and empty text when searching from the end (if instance_num is negative).
⚠ Common Errors
#N/A
By default, #N/A is returned. Excel returns a #N/A error if delimiter isn't contained in text. Excel returns a #N/A error if instance_num is greater than the number of occurrences of delimiter.
Fix: Check the delimiter or specify a fallback value in the last argument
#VALUE!
Excel returns a #VALUE! error if instance_num = 0 or if instance_num is greater than the length of text.
Fix: برای instance_num مقدار غیرصفر و در بازه مجاز وارد کنید.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!