Returns text that occurs after given character or string. It is the opposite of the TEXTBEFORE function.
Syntax
=TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
The TEXTAFTER function syntax has the following arguments:
text The text you are searching within. Wildcard characters not allowed. Required.
delimiter The text that marks the point after which you want to extract. Required.
instance_num 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. Optional.
match_mode Determines whether the text search is case-sensitive. The default is case-sensitive. Optional. Enter one of the following:
-
0 Case sensitive.
-
1 Case insensitive.
match_end Treats the end of text as a delimiter. By default, the text is an exact match. Optional. Enter one of the following:
-
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 Value returned if no match is found. By default, #N/A is returned. Optional.
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).
|
Example |
Result |
|---|---|
|
=TEXTAFTER("Red riding hood’s, red hood", "hood") |
’s, red hood |
|
=TEXTAFTER("Red riding hood’s, red hood", "") |
Red riding hood’s, red hood |
|
=TEXTAFTER("Red riding hood’s, red hood", "", -1) |
Errors
-
Excel returns a #N/A error if delimiter isn't contained in text.
-
Excel returns a #VALUE! error if instance_num = 0 or if instance_num is greater than the length of text.
-
Excel returns a #N/A error if instance_num is greater than the number of occurrences of delimiter.
Examples
Copy the example data in each of the following tables and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.
|
Data |
||
|
Little Red Riding Hood's red hood |
||
|
Little red Riding Hood's red hood |
||
|
Formulas |
Results |
|
|
=TEXTAFTER(A2,"Red") |
Riding Hood's red hood |
|
|
=TEXTAFTER(A2,"basket") |
#N/A |
|
|
=TEXTAFTER(A3,"red",2) |
hood |
|
|
=TEXTAFTER(A3,"red",-2) |
Riding Hood's red hood |
|
|
=TEXTAFTER(A3,"Red",,FALSE) |
#N/A |
|
|
=TEXTAFTER(A2,"red",3) |
#N/A |
|
Data |
|
|
Marcus Aurelius |
|
|
Socrates |
|
|
Immanuel Kant |
|
|
Formulas |
Results |
|
=TEXTAFTER(A2," ",,,1) |
Aurelius |
|
=TEXTAFTER(A3," ",,,0) |
#N/A |
|
=TEXTAFTER(A3," ",,,1) |
|
|
=IF(TEXTAFTER(A3," ",,,1)="",A3) |
Socrates |
|
=TEXTAFTER(A4," ",,,1) |
Kant |
