TEXTBEFORE function

The TEXTBEFORE function in Excel is used to extract the text that appears before a specified delimiter within a text string. This function is helpful when you need to retrieve part of a string before a specific substring or character, such as extracting the portion before a comma, space, or any other delimiter.

Syntax:

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

Parameters:

• text (required): The original text string from which you want to extract the text before the delimiter.
• delimiter (required): The substring or character that marks the point before 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 specify a number to extract the text before a specific instance of the delimiter.
• 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 of the search:
  • 1 (default) for a forward search (left to right).
  • -1 for a reverse search (right to left).

Key Points:

• The function returns the text before the first (or specified) occurrence of the delimiter.
• You can control case sensitivity and the direction of the search with the optional arguments.
• The TEXTBEFORE function is useful for parsing and analyzing text data, especially when the text follows a consistent pattern or delimiter.

Examples of Usage:

1. Extract Text Before the First Occurrence of a Delimiter

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

=TEXTBEFORE(A1, ", ")

This will return:

apple

Explanation:

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

2. Extract Text Before a Specific Instance of a Delimiter

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

=TEXTBEFORE(B1, ", ", 2)

This will return:

apple, banana

Explanation:

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

3. Case Sensitivity

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

=TEXTBEFORE(C1, "Banana", 1, 1)

This will return:

apple,

Explanation:

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

4. Search in Reverse

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

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

This will return:

apple, banana, cherry

Explanation:

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

5. Extract Text Before a Specific Character

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

=TEXTBEFORE(E1, ": ")

This will return:

ID

Explanation:

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

Related Functions:

• TEXTAFTER: Extracts the text that comes after a specified delimiter, opposite of TEXTBEFORE.
• MID: Extracts a substring from a text string based on a specific position and length, useful when you want to extract text from a known position 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 TEXTBEFORE.

The TEXTBEFORE function is very powerful for text manipulation, especially in scenarios where you need to parse data structured with delimiters, such as extracting the first name from a full name or the domain name from an email address.