TEXTAFTER function

Release notes

Current Channel

Monthly Enterprise Channel

Semi-Annual Enterprise Channel

Semi-Annual Enterprise Channel (Preview)

Office for Mac

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.

Example 1

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

Example 2

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