Nov 174 min read

Extract text in Excel with the TEXTBEFORE and TEXTAFTER functions

Updated: Nov 27

If you need to extract text before or after a fixed value (a delimiter) in text, then the TEXTBEFORE and TEXTAFTER functions can detect a given value in a text string and extract everything before or after this value.

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 the same:

=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.

TEXTBEFORE

Using just the compulsory criteria for the TEXTBEFORE function a cell can be selected for text and for the delimiter we can enter “ – “ which is the character separating the words in the text written in the cell.

As the delimiter is set the function can be used across a range of text and will always split the word where it finds the character “ – “ and extract the text before this, it can be written once and dragged down a range.

TEXTAFTER

The TEXTAFTER function works in a similar way to TEXTBEFORE.

There are also only two compulsory criteria which are text and delimiter.

Using the same examples when using “-“ as the delimiter the TEXTAFTER function returns all the text after the delimiter.

Using different delimiters in the same function

In the example above when the functions were dragged down they returned the correct values because all the delimiters are the same.

If there are multiple delimiters in a dataset when you drag the functions down they will return an error for the cells that do not contain the delimiter specified in the function.

To include more than one delimiter in either function the curly brackets can be used to add multiple delimiters.

Each delimiter need to be inside double quotation marks and separated with a comma.

Using the instance_num

If you have more than one instance of a delimiter both TEXTBEFORE and TEXTAFTER will default to the first instance that it finds it.

Using the instance_num parameter you can change the instance that you split the text.

The instance_num can also be used to dynamically always use the last instance of a delimiter to split the text. To do this enter -1 in the instance_num parameter.

Using match_mode

If you want to use a word as the delimiter to split your text, then as a default both TEXTBEFORE and TEXTAFTER are case sensitive.

Using the match_mode parameter you can tell the function to disregard upper and lower case and just match words based on the letters used regardless of the case they were written in.

When entering the match_mode you can enter one of two options:

0 – Case sensitive match – Searches for a case sensitive delimiter match

1 – Case insensitive match - Searches for a case insensitive delimiter match

As the default for both functions is a case sensitive match, you don’t need to enter zero, but if you want the match to be case insensitive then you can enter 1 in the match_mode.

Using match_end

If a delimiter is not found in a text string then both TEXTBEFORE and TEXTAFTER return the ‘#N/A’ error.

When using match_end instead of returning an error when a delimiter is not found, the function will treat the end of the text as the delimiter.

When entering the match_end you can enter one of two options:

0 - Don't match to end – If a match could not be found for the delimiter, return '#N/A'

1 - Match to end – Match the delimiter to the end of the text

As the default for both functions is a to return ‘#N/A’, you don’t need to enter zero, but if you want to match to the end you can enter 1 in match_end.

Match_end returns different results for both functions.

For TEXTBEFORE entering 1 will return the whole text.

To be able to use match_end with TEXTAFTER to return the whole text a -1 needs to be entered in instance_num.

Using if_not_found

While the default output when a delimiter is not found is the ‘#N/A’ error, if you want to chose what happens if the delimiter is not found you can use the if_not_found parameter.

This allows you to enter any value you want returned if the delimiter is not found.

For example if the delimiter was "North East" and if that was not in any text you were using one of the functions on, you could set the if_not_found to "Cannot find North East" and this is what the function would return.

Conclusion

The TEXTBEFORE and TEXTAFTER functions can be used to dynamically extract text of different lengths either before or after a delimiter.

As a default you only need to enter a text value and a delimiter, and if the delimiter appears more than once both functions will use the first instance of the delimiter.

Using the optional criteria in the functions you can change the instance of which delimiter should be used if it is there more than once, or if you are using text as a delimiter choose whether the function should include or ignore upper or lower case when using the text.

If the delimiter is not found in the text then the optional match_end or if_not_found criteria can be used to either use the end of the text as the delimiter or to create a custom output to replace the default '#N/A' error.