TEXTAFTER function

The TEXTAFTER function in Excel is used to extract the text that comes after a specified substring within a text string. It allows you to extract a portion of text that follows a particular delimiter or substring, making it especially useful for parsing and analyzing text data.

Syntax:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [search_mode])

Parameters:

• text (required): The original text string from which you want to extract data.
• delimiter (required): The substring or character that marks the point after which the remaining text will be returned.
• instance_num (optional): Specifies which occurrence of the delimiter to search for. By default, it searches for the first occurrence (1). You can provide a number to specify which instance to use (e.g., the second or third occurrence).
• match_mode (optional): Defines whether the match is case-sensitive (1) or not (0). The default is case-insensitive (0).
• search_mode (optional): Specifies the direction to search for the delimiter:
  • 1 (default) for a forward search (left to right).
  • -1 for a reverse search (right to left).

Key Points:

• The function returns the text after the first (or specified) occurrence of the delimiter.
• You can control case sensitivity and the direction of the search with the optional arguments.
• The TEXTAFTER function is useful for text parsing, especially when working with strings that have a consistent delimiter.

Examples of Usage:

1. Extract Text After a Single Occurrence of a Delimiter

If cell A1 contains the text "apple, banana, cherry" and you want to extract the text after the comma (,) and space:

=TEXTAFTER(A1, ", ")

This will return:

banana, cherry

Explanation:

• The function looks for the first occurrence of ", " (comma and space) and returns everything that comes after it.

2. Extract Text After a Specific Instance of a Delimiter

If cell B1 contains "apple, banana, cherry, date", and you want to extract the text after the second comma:

=TEXTAFTER(B1, ", ", 2)

This will return:

cherry, date

Explanation:

• The function looks for the second occurrence of ", " (comma and space) and returns everything that comes after it.

3. Case Sensitivity

If cell C1 contains "apple, Banana, cherry", and you want to extract the text after “banana” (case-sensitive):

=TEXTAFTER(C1, "banana", 1, 1)

This will return:

cherry

Explanation:

• The function looks for the first occurrence of "banana" (case-sensitive) and returns the text after it.

4. Search in Reverse

If cell D1 contains "apple, banana, cherry, date", and you want to extract the text after the last comma, you can use the reverse search mode:

=TEXTAFTER(D1, ", ", -1)

This will return:

date

Explanation:

• The function searches from right to left and returns the text after the last occurrence of ", " (comma and space).

5. Extract Text After a Specific Character

If cell E1 contains "ID: 12345", and you want to extract the text after the colon (:):

=TEXTAFTER(E1, ": ")

This will return:

12345

Explanation:

• The function looks for ": " and returns the text that comes after it.

Related Functions:

• TEXTBEFORE: Extracts the text that comes before a specified delimiter.
• MID: Extracts a substring from a text string based on a specific position and length, but without a delimiter.
• SEARCH: Finds the position of a substring within a string, useful for determining where to extract text with functions like MID or TEXTAFTER.

The TEXTAFTER function is a powerful tool for string manipulation, particularly when working with structured data where delimiters are used consistently to separate different parts of the text.