Lesson#162: What are TEXTBEFORE and TEXTAFTER functions in Excel?

Microsoft Excel has introduced some new functions in 2022. Two of them are the Excel TEXTBEFORE and TEXTAFTER functions.

What is TEXTBEFORE function?

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.

How to use TEXTBEFORE function?

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.

What is TEXTAFTER function?

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

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

text       The text you are searching within. Wildcard characters are not allowed. Required.

delimiter       The text that marks the point after 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 one of 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.

How to use TEXTAFTER function?

Example 1:

Example 2:

In this example, I have changed the instance_num to 3.

Example 3:

If I change the match_mode to 1 which is case insensitive then shows me this result.

If we change the match_mode to 0 which is case sensitive then it shows a different result.

Example 4:

Here I have put the instance_num in negative which means counting from backward. match_end is here 0.

Then I changed the match_end to 1.

Now it assumes a delimiter before the text so it returns 1.

Example 5:

if_not_found is only needed to customize the error value.

How to get TEXTBEFORE and TEXTAFTER functions?

Currently, TEXTBEFORE and TEXTAFTER are available on Excel for Microsoft 365 (Windows), Excel for Microsoft 365 (Mac), and  Excel for the web only.

Join our Telegram channel https://t.me/excelabcd

Here you shall be regularly updated with Excel tips and tricks.