SpreadsheetWeb blog
Excel TEXTAFTER Function
In this guide, we’re going to show you how to use the TEXTAFTER function and go over some tips and error handling methods.
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.