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.
|
| match_end |
Optional. Treats the end of text as a delimiter.
|
| 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"
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!"
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.
