Excel TEXTBEFORE function - extract text before character (delimiter)

Excel TEXTBEFORE function

The TEXTBEFORE function in Excel is specially designed to return the text that occurs before a given character or substring (delimiter). In case the delimiter appears in the cell multiple times, the function can return text before a specific occurrence. If the delimiter is not found, you can return your own text or the original string.

The syntax of the TEXTBEFORE function has 6 arguments, of which only the first two are required.

text (required) - the original text to extract from. Can be supplied as a string or cell reference.

delimiter (required) - a character or substring that delimits the text.

instance_num (optional) - the instance of the delimiter before which to extract text. Default is 1. A negative number starts searching from the end of the original text.

match_mode (optional) - determines the delimiter's case-sensitivity. Enabled by default.

• 0 (default) - case-sensitive
• 1 - case-insensitive

match_end (optional) - treats end of text as delimiter. Disabled by default.

• 0 (default) - match the delimiter exactly as specified in the formula.
• 1 - match the delimiter against the end of text. Practically, it means that if the delimiter is not found, the function will return the original text.

if_not_found (optional) - the value to return if the delimiter is not found. If not set, a #N/A error is returned.

Excel formula to extract text before character

For starters, let's get to know how to build a TEXTBEFORE formula in its simplest form.

Supposing you have a list of full names in column A and want to extract the first name that appears before the comma.

That can be done with this basic formula:

=TEXTBEFORE(A2, ",")

Where A2 is the original text string and a comma (",") is the delimiter.

Extract text before first space in Excel

To get text before a space in a string, just use the space character for the delimiter (" ").

=TEXTBEFORE(A2, " ")

Since the instance_num argument is set to 1 by default, the formula will return text that appears before the first space.

Get text before Nth instance of delimiter

To extract text that appears before the nth occurrence of the delimiter, supply the number for the instance_num parameter.

For example, to get text before the second occurrence of a comma, the formula is:

=TEXTBEFORE(A2, ",", 2)

Return text before last occurrence of delimiter

To return text before the last occurrence of the specified character, put a negative value in the instance_num argument.

For example, to return text before the last comma in A2, the formula is:

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

To extract text before the last but one comma, set instance_num to -2:

=TEXTBEFORE(A2, ",", -2)

Extract text before substring

The delimiter argument of TEXTBEFORE can be a substring, i.e. a sequence of characters, which adds even more value to the function.

Let's say you have a list of employee names and their positions in one column, separated by a hyphen. The problem is that some names are hyphenated. To avoid splitting such names, we use a substring consisting of a space and a hyphen (" -") for the delimiter:

=TEXTBEFORE(A2, " -")

Get text before multiple delimiters

To handle different variations of the delimiter, you can use an array constant like {"a","b","c"} in your TEXTBEFORE formula, where a, b, and c represent different delimiters.

For example, if the delimiter is either a comma or a hyphen, with or without the preceding space character, you can handle all four variations correctly by providing the array constant {","," ,","-"," -"} for delimiter:

=TEXTBEFORE(A2, {","," ,","-"," -"})

If delimiter is not found, return custom text

If the specified delimiter is not found, the TEXTBEFORE function returns a #N/A error by default. To return a custom value instead, configure the if_not_found argument (the last one).

For example, if not a single comma appears in the source text (A2), this basic formula throws a #N/A error:

=TEXTBEFORE(A2, ",")

To get rid of the errors, type any text you want in the final argument, e.g. "Not found":

=TEXTBEFORE(A2, ",", , , , "Not found")

To return nothing, i.e. a blank cell, use an empty string for if_not_found:

=TEXTBEFORE(A2, ",", , , , "")

If delimiter is not found, return original text

In some situations, it makes sense to return the original text if the TEXTBEFORE function cannot match the delimiter in the string. For this, just set the match_end argument (the 5^th one) to 1. This will tell the formula to treat the end of text as delimiter.

=TEXTBEFORE(A2, ",", , ,1)

As you can see in the screenshot below, the formula now returns an employee name even if the delimiter (which is a comma) is not present in a cell.

Make delimiter case-sensitive or case-insensitive

By default, the Excel TEXTBEFORE function is case-sensitive, meaning it treats lowercase and uppercase delimiters as different ones. To disable case-sensitivity, set the match_mode argument (the 4^th one) to 1 or TRUE.

For example, the below formula only accepts the lowercase "x" as the delimiter:

=TEXTBEFORE(A2, "x")

Whereas an analogous formula with match_mode set to 1, recorgnizes both "x" and "X" as the delimiter:

=TEXTBEFORE(A2, "x", ,1)

TEXTBEFORE function not working

If the TEXTBEFORE function does not work as expected or is not available in your Excel, it's likely to be one of these reasons.

If TEXTBEFORE does not exist in your Excel, please check out the list of supported versions.

If TEXTBEFORE returns a #N/A error, then either:

• The delimiter does not exist in the source text. To handle the error, configure the if_not_found argument as explained in this example.
• The instance_num value is greater than the number of the delimiter's occurrences in text.

If TEXTBEFORE returns a #VALUE! error, then either:

• The instance_num argument is set to 0.
• The instance_num value is greater than the total length of text.

Practice workbook for download

TEXTBEFORE function – formula examples (.xlsx file)