Explanation

TEXTBEFORE and TEXTAFTER are functions that can help you extract a text before or after a certain delimiter. These two functions are extremely powerful and they let us perform text extraction tasks in a much simpler way than what we were used to! (In the past these tasks were quite tedious, and we needed to use a combination of functions such as LEFT, RIGHT, MID, FIND, LEN, etc.)

Please note that these functions are only available for Excel 365 & Excel for the Web. 

Examples

TEXTBEFORE

Let’s start with a simple example that shows how we can extract names from email addresses:

TEXTAFTER

And now, let’s see how we can benefit from TEXTAFTER to extract the name of each state:

Syntax

TEXTBEFORE Syntax:

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

TEXTAFTER Syntax:

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

It’s easy to see that the syntax of both functions is EXACTLY the same (except for the name of the function of course…)

Let’s drill down through the arguments that we can use for each of these functions:

text – This is the original text (or cell) from which we wish to extract a specific text before/after the delimiter we’ll choose

delimiter – What’s the delimiter character (or a number of characters – We can actually use entire words as delimiters) that in case found in our text, we’ll want to extract everything before/after this character?

Note that you must use double quotes when typing the delimiter, as you are entering textual input into the function. Examples for common delimiters – “@” (At email sign), “,” (Comma), “.” (Dot), etc.  

The next 4 arguments ([instance_num], [match_mode], [match_end], [if_not_found]) are optional. You are not obliged to use these every time you write a function (Although some might be quite helpful in some cases, as we’ll see in the next examples 🙂 )

[instance_num] – In case there are several delimiters of the same kind in the text – Which instance of delimiter should be taken? 

For example – Let’s see how we can extract the last 3 digits after the 2nd occurrence of the “-” delimiter:

If we omit the instance_num argument, the default instance_num will be 1 (the first time we see the delimiter).

Normally, TEXTBEFORE and TEXTAFTER start searching for the delimiter from the start of the text. But what if we want to perform the search from the end of the text? For that, we can type a negative instance number, and then we will search for the Nth instance from the end. Let’s see an example:

match_mode – Should the search for delimiter be case-sensitive or not? 0 (Which is the default) is case sensitive, and you can type 1 if you wish to do a case insensitive search.

Let’s see an example for case sensitive vs case insensitive delimiter search:

We can see that in the case insensitive example, we don’t care if the s is uppercase or lowercase, and therefore we find an earlier match.

Tip – As seen in the example above, we can skip optional delimiters by just typing an additional comma ( , ) – that will take us to the next argument. 

match_end – This argument allows us to choose whether we want to consider the end of the text as a delimiter as well. If we type 0 (default), it won’t match. However, if we type 1, it will match the end of text as a delimiter as well. 

We can think of it as adding an additional imaginary delimiter of the same kind we are looking for. Confused? Let’s see an example:

As you can see, there are only 2 “-” delimiters in the example above. When match_end is 0, we are not treating the end of the text as a delimiter, therefore we get an #N/A error – we can’t find the 3rd delimiter, so an error is returned.

However, when we use match_end = 1, we treat the end of the text as the same delimiter. So basically we have now n+1 delimiters, and in this example, we will return all the text before the end of the text (which is basically… all of the text 🙂 )

if_not_found – What message should appear if the delimiter we were looking for was not found? By default, we’ll get an #N/A, however, if we wish to have a more user-friendly message, we can type our message within this argument. Let’s see an example:

 

Finding text between delimiters

There is no function to get the text between delimiters, but we can create one, by combining TEXTBEFORE and TEXTAFTER. For example, let’s try to get the domain names of the following websites:

In this example, we first use TEXTAFTER to get the text after the “.” delimiter – The result is “excel-practice-online.com/”

This result is passed to the TEXTBEFORE formula, which now has as input only one dot delimiter, therefore it returns “excel-practice-online”

Please note – In some cases, the order of TEXTBEFORE/TEXTAFTER is not important – We can either start with a TEXTBEFORE followed by TEXTAFTER, or we can do the opposite – start with TEXTAFTER, then followed by TEXTBEFORE. 

Using Multiple delimiters

We can search for multiple delimiters in our text and extract anything before/after any of these delimiters – which is pretty handy in real life, as sometimes there is more than one delimiter type in our data. To use more than one delimiter, we can type the list of delimiters, separated by a comma, within an array – by typing the values within curly brackets – { } 

Practice TEXTBEFORE and TEXTAFTER

Let’s put our knowledge to the test! Let’s practice the TEXTBEFORE and TEXTAFTER functions!