(Office Insider Beta only)
Windows: 2203 (Build 15104)
Mac: 16.60 (220304)

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

See Also

Text functions (reference)

TEXTBEFORE function

TEXTSPLIT function

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×