Excel TEXTBEFORE function

Function              TEXTBEFORE

Description        Excel TEXTBEFORE function Returns text that occurs before a given character or string.

Syntax  TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

text       The text you are searching within. Wildcard characters are not allowed. If the text is an empty string, Excel returns empty text. Required.

delimiter       The text that marks the point before which you want to extract. Required.

instance_num       The instance of the delimiter after which you want to extract the text.   By default, instance_num = 1.  A negative number starts searching text from the end. Optional.

match_mode    Determines whether the text search is case-sensitive. The default is case-sensitive. Optional. Enter one of the following:

0      Case sensitive.

1      Case insensitive.

match_end     Treats the end of the text as a delimiter. By default, the text is an exact match. Optional. Enter the following:

0      Don’t match the delimiter against the end of the text.

1      Match the delimiter against the end of the text.

if_not_found    Value returned if no match is found. By default, #N/A is returned. Optional.

Example: 1

Example 2:

Here I have added match_mode as 0, which means it is case sensitive.

What happens if I change the case of the delimiter?

It shall return #N/A error.

Example 3:

In this case, I have taken the delimiter as “e” and match_mode is 0 which is case sensitive and instance_num is 2.

What if I just change the match_mode to 1 which is case insensitive?

Example 4:

In the above example, what if we put the match_end as 0?

It will show a #N/A error because “-” doesn’t appear for the 5th time in the text.

If we put the match_end as 1 then it shows a different result.

This formula acts like there is another “-” delimiter at the end of the text.

Example 5:

if_not_found is for customizing the error value.