Exceljet

Quick, clean, and to the point

Excel TEXTAFTER Function

Excel TEXTAFTER function
Summary 

The Excel TEXTAFTER function returns the text that occurs after a given substring or delimiter. In cases where multiple delimiters appear in the text, TEXTAFTER can return text after the nth occurrence of a delimiter.

Purpose 
Extract text after delimiter
Return value 
Extracted text
Syntax 
=TEXTAFTER (text, delimiter, [instance_num], [ignore_case])
Arguments 
  • text - The text string to extract from.
  • delimiter - The character(s) that delimit the text.
  • instance_num - [optional] The instance of the delimiter in text. Default is 1.
  • ignore_case - [optional] Case-sensitivity. TRUE = off, FALSE = on. Default is FALSE.
Version 
Usage notes 

The Excel TEXTAFTER function extracts text that occurs after a given substring or delimiter. When multiple delimiters appear in the text, TEXTAFTER can return text that occurs after the nth instance of the delimiter. Unlike the TEXTSPLIT function, the output from TEXTAFTER is a single value.

TEXTAFTER takes a total of four arguments, only two of which are required. The first argument, text, is the text string to extract from. The second argument, delimiter is the substring to use when extracting text. Both text and delimiter are required. The third argument, instance_num, is an integer that represents the nth instance of the delimiter in text (i.e. to extract the text after the second instance, use 2 for instance_num). By default, instance_num is 1. The last argument in TEXTAFTER is ignore_case, which is a boolean value that controls behavior when looking for a delimiter. Ignore_case defaults to FALSE, which means TEXTAFTER is case-sensitive by default.

Use TEXTBEFORE to extract text before a delimiter, TEXTAFTER to extract text after a delimiter, and TEXTSPLIT to extract all text separated by delimiters.

Basic usage

To extract the text that occurs after a specific character or substring, provide the text and the character(s) to use as delimiter in double quotes (""). For example, to extract the first name from "Jones, Bob", provide a comma in double quotes (",") as delimiter:

=TEXTAFTER("Jones,Bob",",") // returns "Bob"

You can use more than one character for delimiter. For example to extract the second dimension in the text string "12 ft x 20 ft", use " x "for delimiter:

=TEXTAFTER("12 ft x 20 ft"," x ") // returns "20 ft"

Note we include a space before and after x, since all three characters function as a delimiter.

Nth occurrence of delimiter

To extract text after the nth occurrence of delimiter, provide a value for instance_num. The formulas below extract text after the first and second occurrence of the hyphen character ("-"):

=TEXTAFTER("ABX-112-Red-Y","-",1) // returns "112-Red-Y"
=TEXTAFTER("ABX-112-Red-Y","-",2 // returns "Red-Y"

TEXTAFTER will return #N/A if the specified instance is not found.

Text after last occurrence

TEXTAFTER supports negative values for instance_num, which makes it possible to return text after the last occurrence of delimiter like this:

=TEXTAFTER("ABX-112-Red-Y","-",-1) // returns "Y"
=TEXTAFTER("ABX-112-Red-Y","-",-2) // returns "Red-Y"

If instance_num is out-of-range, TEXTAFTER returns an #N/A error.

Multiple delimiters

To provide multiple delimiters at the same time to TEXTAFTER, you can use an array constant like {"x","y"} where x and y represent different delimiters. One use of this feature is to handle inconsistent delimiters in the source text. For example, in the worksheet below, the delimiter appears as a comma with a space (", ") and a comma without space (","). By providing the array constant {", ",","} for delimiter, both variations are handled correctly:

=TEXTAFTER(B4,{", ",","})

TEXTAFTER with more than one delimiter

Case-sensitivity

By default, TEXTAFTER is case-sensitive when searching for delimiter. This behavior is controlled by the ignore_case argumenta boolean value that enables and disables case-sensitivity. By default, ignore_case is FALSE. In the example below, the delimiter appears as both " x " and " X " (upper and lower case "x"). The formula in D4 sets ignore_case to TRUE, which disables case-sensitivity and allows TEXTAFTER to locate both versions of the delimiter:

=TEXTAFTER(B4," x ",,TRUE) // disable case-sensitivity

TEXTAFTER case sensitive example

Note: you can use 1 and 0 in place of TRUE and FALSE for the ignore_case argument.

Notes

  • TEXTAFTER will return a #VALUE! error if delimiter is not found.
  • TEXTAFTER will return a #VALUE! error if text is empty
  • TEXTAFTER will return #N/A if instance_num is out-of-range.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.