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 nth instance of instance_num that you want to extract. By default, instance_num = 1. A negative number starts searching text from the end. Optional.
match_mode Searches the text for a delimiter match. By default, a case-sensitive match is done. Optional.
match_end Treats the end of text as a wildcard match. By default, the text is an exact match. Optional. Enter 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.
ignore_case Specify FALSE to make the search case-sensitive. The default is TRUE which means the search is case-insensitive. 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.
-
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 |
||
|
Red |
Little Red Riding Hood's red hood |
|
|
basket |
||
|
red |
Little red Riding Hood's red hood |
|
|
Formulas |
Results |
|
|
=TEXTAFTER(B2,A2) |
Riding Hood's red hood |
|
|
=TEXTAFTER(B3,A2) |
#N/A |
|
|
=TEXTAFTER(B4,A4,2) |
hood |
|
|
=TEXTAFTER(B4,A4,-2) |
Riding Hood's red hood |
|
|
=TEXTAFTER(B4,A2,,FALSE) |
#N/A |
|
|
=TEXTAFTER(B4,A4,3) |
#N/A |
