The TEXTAFTER function returns the portion of a text value following the appearance of a sequence of characters designated as being a delimiter. By default, everything following the first appearance of the delimiter is returned; the search for the delimiter is case sensitive; and the end of the text value being searched is not treated as being a delimiter. These behaviours can be overridden through the instance_num, match_mode and match_end arguments.
Invocations of the TEXTAFTER function take the form:
The formula instructs the TEXTAFTER function to perform a case-sensitive search of the text value “AxBxCXDXE” for the delimiter “X”. Since (1) the value supplied to the instance_num argument is negative, the search will traverse text from right to left, and (2) the value supplied to the match_end argument is 1 the left end of text will be treated as being a delimiter. The first instance of “X” found by the search is between “D” and “E” and the second between “C” and “D”. The search ignores the “x” between “B” and “C”, and “A” and “B” since they are lower case. Finally, the search reaches the point preceding “A” which is treated as being the 3rd delimiter. TEXTAFTER then returns everything following it, which is “AxBxCXDXE”. The complete process is illustrated below:
A text value that will be searched for the value supplied to the delimiter argument. The portion of text following the appearance of delimiter will be returned by TEXTAFTER.
delimiter
Mandatory
A sequence of 1 or more characters whose appearance in text marks the boundary between what will and will not be returned by TEXTAFTER.
instance_num
Optional. Defaults to 1 if omitted.
An integer used to determine which instance of delimiter will be searched for. If instance_num is positive then text will be searched from left to right. If it is negative then text will be searched from right to left. Consider a situation in which text had the value “A-B-C-D” and delimiter had the value “-“. If instance_num were 3 then a left-to-right search for “-” would be conducted and “D” returned. If it were -3 then a right-to-left search would be conducted and “B-C-D” would be returned.
match_mode
Optional. Defaults to 0 if omitted.
Determines if TEXTAFTER’s search for delimiter in text is case sensitive.
Value
Description
0
A case-sensitive search for delimiter in text is conducted.
1
A case-insensitive search for delimiter in text is conducted.
match_end
Optional. Defaults to 0 if omitted.
Determines if an end of text will be treated as being a delimiter. If match_end were set to 1 and instance_num were positive then the the right end of text would be treated as a delimiter. Conversely, if instance_num were negative then the left end of text would be treated as a delimiter. Consider a situation in which match_mode were set to 1, text had the value “A-B-C-D” and delimiter had the value “-“. If instance_num were 4 then TEXTAFTER would treat the right end of text as the fourth delimiter and return the empty text value (“”). If it were -4 then the left end would be treated as the fourth delimiter and “A-B-C-D” would be returned.
Value
Description
0
The end of text will not be treated as a delimiter.
1
The end of text will be treated as a delimiter.
if_not_found
Optional. Defaults to #N/A if omitted.
The value returned if no instance of delimiter exists in text which meets the search criteria.
The value supplied to the argument instance_num was 0.
#N/A
Runtime
It wasn’t possible to find an instance of the delimiter using the search criteria.
Examples
Example
Formula
Result
Description
1
=TEXTAFTER("A-B-C","-",-2)
B-C
Perform a right-to-left search for the second “–” in “A-B-C“.
The “–” between “B-C” is the first delimiter found.
The “–” between “A-B” is the second delimiter found.
TEXTAFTER returns all of “A-B-C” following the second delimiter.
2
=TEXTAFTER("A-B-C","-",2)
C
Perform a left-to-right search for the second “–” in “A-B-C“.
The “–” between “A-B” is the first delimiter found.
The “–” between “B-C” is the second delimiter found.
TEXTAFTER returns all of “A-B-C” following the second delimiter.
3
=TEXTAFTER("AxBXC","X",1,0)
C
Perform a case-sensitive & left-to-right search for the first “X” in “AxBXC“.
Because it is lower case, the “x” between “A” and “B” is ignored.
The “X” between “B” and “C” is the first delimiter.
TEXTAFTER returns all of “AxBXC” following the first delimiter.
4
=TEXTAFTER("AxBXC","X",1,1)
BXC
Perform a case-insensitive & left-to-right search for the first “X” in “AxBXC“.
The “x” between “A” and “B” is the first delimiter.
TEXTAFTER returns all of “AxBXC” following the first delimiter.
5
=TEXTAFTER("A-B-C","-",3,1,1)
Treat the end of “A-B-C” as a delimiter and search it from left-to-right for the third “–“.
The “–” between “A” and “B” is the first delimiter.
The “–” between “B” and “C” is the second delimiter.
Because match_end is 1, the end of “A-B-C” is the third delimiter.
TEXTAFTER returns all of “A-B-C” following the third delimiter.
6
=TEXTAFTER("A-B-C","-",3,1,0,"?")
?
Perform a case-insensitive & left-to-right search for the third “–” in “A-B-C“. Its end is not treated as being a delimiter and “?” will be returned if the search fails.
The “–” between “A” and “B” is the first delimiter.
The “–” between “B” and “C” is the second delimiter.
Because match_end is 0, no more delimiters are found.
Because the search for the third “–” failed, “?” is returned.
Examples: Errors
Example
Formula
Result
Description
1
=TEXTAFTER("AxBXC","X",2,0)
#N/A
Perform a case-sensitive & left-to-right search for the second “X” in “AxBXC“. Because TEXTAFTER fails to find it and no value was supplied to if_not_found, #N/A is returned.
2
=TEXTAFTER("A-B-C","-",0)
#VALUE
Supply a value of “0” to the instance_num argument.
