Nov 242 min read

Combine TEXTBEFORE and TEXTAFTER to get text between delimiters in Excel

There is no function in Excel to get text between two delimiters, but the TEXTBEFORE and TEXTAFTER functions can be used together to achieve this.

Download File

If you would like to follow along download the attachment below.

What are the TEXTBEFORE and TEXTAFTER functions

The TEXTBEFORE and TEXTAFTER functions are text functions that extract text either before or after a delimiter.

A delimiter is anything that divides text, it can be punctuation, like a comma or a full stop, text, like a specific word or even a symbol like the @ sign.

The syntax for each function is quite similar:

=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: The text string or cell reference containing the text you want to parse.

delimiter: The character or string that separates the text (like a space, comma, or dash).

instance_num (optional): Specifies which instance of the delimiter to use.

match_mode (optional): Allows case sensitivity (0 for case-insensitive, 1 for case-sensitive).

match_end (optional): Searches for the delimiter from the end of the text.

if_not_found (optional): Value to return if the delimiter isn't found.

The examples in this blog will cover the text and delimiter segments of the functions. To learn more about the optional segments click here.

Using TEXTBEFORE and TEXTAFTER individually

TEXTBEFORE and TEXTAFTER will select all text before or after a single delimiter.

This means that when used on their own you cannot find text that is between two delimiters.

If you had a text string consisting of three words separated by the delimiter “ – “, and you wanted to extract the word in the middle between the two delimiters, using TEXTBEFORE would extract everything before the first instance of the delimiter and TEXTAFTER would extract everything after the first instance of the delimiter.

Combining TEXTBEFORE and TEXTAFTER to get text between delimiters

To get the text between the two delimiters the TEXTAFTER function can be nested in the TEXTBEFORE function.

Once the TEXTAFTER function has been nested into the TEXTBEFORE function it can be filled out as normal.

As the text part of the TEXTBEFORE function is made up of the TEXTAFTER function, the delimiter segment for TEXTBEFORE can be added, which is again “ – “.

The result is that the two functions combined return the text between the two “ – “.

Once the function combining TEXTBEFORE and TEXTAFTER has been finished it can be dragged across a dataset to extract everything between the delimiters regardless of the size of text before or after the delimiters.

Conclusion

There is no function in Excel to extract text between two delimiters, but by nesting a TEXTAFTER into a TEXTBEFORE this can be achieved.

The value TEXTAFTER returns becomes the text component of the TEXTBEFORE function, so the TEXTBEFORE will extract everything before the delimiter it has been specified from the text that the TEXTAFTER function has already extracted.