The TEXTAFTER function is a Text function that returns part after a given character or string. In this guide, we’re going to show you how to use the TEXTAFTER function and go over some tips and error-handling methods.

Supported versions

  • Excel 365 (Office Insider Beta only)

TEXTAFTER Function Syntax

TEXTAFTER(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

Arguments

text The text you want to split.
delimiter The text to delimit the part you want to extract.
instance_num Optional. The instance of the delimiter. The default is 1, which means to extract by the first delimiter.
Enter a negative value to start search from end.
match_mode

Optional. Predefined integer to determine case-sensitivity at delimiter matching.

  • 0: Case-sensitive (default)
  • 1: Not case-sensitive
match_end

Optional. Treats the end of text as a delimiter.

  • 0: Disabled (default)
  • 1: Enabled
if_not_found Optional. Value returned if there is not a match. By default, #N/A is returned.

TEXTAFTER Function Example

Default Usage

The TEXTAFTER function requires the string (text) and the delimiter string to return a string after a given delimiter with default options.

=TEXTAFTER("Wonder Woman"," ") // returns "Woman"
=TEXTAFTER("Spider-Man","-") // returns "Man"

Excel TEXTAFTER Function 01 - Default

Multiple delimiters

If your source data contains multiple delimiters, you can enter multiple delimiters as an array value.

=TEXTAFTER("Spider-Man",{"-"," ","."}) //returns "Man"
=TEXTAFTER("Wonder Woman",{"-"," ","."}) // returns "Woman"
=TEXTAFTER("Dr.Manhattan",{"-"," ","."}) // returns "Manhattan"

Text after n-th delimiter

If you want to skip first delimiter(s) after parsing the text, give the instance number of the target delimiter as instance_num argument.

=TEXTAFTER("Wonder Woman","Wo",2) // returns text after 2nd delimiter: "man"

Text after -n-th delimiter

You can give instance_num argument to a negative number to return text after the last occurrence of delimiter.

=TEXTAFTER("Wonder Woman","-",-1) // returns "man"
=TEXTAFTER("Wonder Woman","-",-2) // returns "nder Woman"
=TEXTAFTER("Wonder Woman","-",-3) // returns #N/A

Case-sensitive delimiter matching

By default, the TEXTAFTER function applies a case-sensitive search to find the delimiters. You can make the delimiter search not case-sensitive by giving "1" to the match_mode parameter.

=TEXTAFTER("Silver Surfer","silver ",,1) // returns "Surfer"
=TEXTAFTER("Quicksilver (Earth-127)","silver ",,1) // returns "(Earth-127)"

Match end of the text

You can determine that end of the text is treated as a delimiter or not. This feature is disabled by default. You can enable it to return entire text if delimiter doesn't exist.

=TEXTAFTER("The One-Above-All","-") // returns #N/A error by default
=TEXTAFTER("The One-Above-All","-",,,1) // returns "The One-Above-All" when match_end equals 1

If a delimiter is not found

You can define a custom text or cell reference to return when the function cannot find the given delimiter(s).

=TEXTAFTER("Wonder Woman","-",,,,"Delimiter is not in this castle!") // returns "Delimiter is not in this castle!"

Excel TEXTAFTER Function 07 - If not found

Download Workbook

Remarks

  • The function doesn't return the delimiter string or character itself.
  • Use the TEXTBEFORE function to retrieve a part before the delimiter.
  • If you need to split a text into multiple parts, prefer TEXTSPLIT function.
  • If delimiter is not found, the function returns #N/A error.