In Excel, if you need to dynamically extract a portion of text, you can move beyond the basic LEFT and RIGHT functions.

In Excel, to extract text from a string there are several functions and combinations (more or less easy to use). Within Microsoft 365, new features make it easier.

TEXTBEFORE and TEXTAFTER functions

The TEXTBEFORE and TEXTAFTER allows you to extract a string before or after a delimiter. The latter can be a symbol as well as a text. These functions work as follows:

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

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

  • Text: is the entire text from which we want to extract a part. This can be text (quoted in quotation marks) or a cell reference
  • Delimiter: is the element that distinguishes the text you want to sample.
  • Instance_num: if the delimiter appears more than once, it is possible to specify whether to take the 1st and the Nth (optional argument).
  • match_mode: specify whether the case must be respected (upper/lowercase) and thus the reported text will have the same case as the original (optional argument, 0 by default).
    • 0: if case must be respected
    • 1: if the case doesn‘t matter
  • Match_end: Treats the end of the text as a delimiter. By default, the text matches exactly. (optional argument).
    • 0: Not matching the delimiter to the end of the text.
    • 1: Match the delimiter to the end of the text.
  • if_not_found: The value to be displayed if no value is found. (optional argument, default N/A).

Examples of use

From examples, let‘s look at how these 2 functions work.

In the 1st examples, we will only use the mandatory arguments (the first 2).

The text in which we want to extract a part is in column A.

We use the TEXTBEFORE and TEXTAFTER functions to see the difference.

In the 1st example, the delimiter is the @ symbol. We can see that the TextBefore function retrieves all the text before the @. The TextAfter function retrieves all text after the @.

In the 2nd example, the delimiter is the symbol – surrounded by spaces. So in the formula, we will be careful to put the spaces as well.

In the 3rd example, the delimiter is the words ‘s. In the formula, we omitted to put the spaces in the delimiter, so we can see that the result of the TextAfter has a space before the word son.

These examples show us that the delimiter can be of several different natures. It could also be several words or other.

In these examples, we use the Instance_num. From the “Yesterday–Today–Tomorrow“ sequence we will extract part of the text. The symbol – is the delimiter. In this text, this delimiter appears several times. We can choose which one to take.

In the 1st example, we did not provide Instance_num. By default, the 1st is used.

In the 2nd example, we put 2 in Instance_num. Thus we extract everything that is before or after the 2nd dash.

In these examples, let‘s see how to use negative values in the Instance_num. From the sequence “Yesterday–Today–Tomorrow“ and the hyphen as a delimiter, let‘s see how it works.

Negative values are used to look for delimiters from the end.

So in the 1st example, we put –1 in Instance_num. Thus, the extracted text is before or after the last delimiter.

In the 2nd example, we put –2 in Instance_num. Thus everything before or after the penultimate dash is extracted.

This can be handy when you want to retrieve an extension or that kind of data.