With TEXTBEFORE, you can extract text before a specific character (delimiter), like pulling the street name from a full address. TEXTAFTER does the opposite, letting you take what’s after a delimiter, such as extracting a product model from its full name.

These tools are incredibly handy for organizing data or finding details fast.

In this quick guide, we’ll show you how these functions can make extracting text in Excel a breeze. Let’s get started and unlock these time-saving tricks.

Extract Text Before or After a Defined Character

Here in Austria, people are fond of collecting titles for their names.

Suppose we receive a list of titles and names where we need to separate the titles from the names.

In the “Old Days”, separating the names from the titles would mean writing a formula the length of your arm. These days, it’s simple. The logic may seem complicated, but the solution is not.

Some users have one title, some have multiple titles, while others have no titles at all.

The functions that have come to our rescue are the new TEXTBEFORE and TEXTAFTER functions in Excel.

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

How to Use the TEXTBEFORE Function

We’ll begin by using the TEXTBEFORE function to extract the titles from the text.

The syntax for the TEXTBEFORE function is as follows:

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

Using the first name in the list located in cell A1

…we need to extract all text before the last occurrence of a “period-space” character set.

=TEXTBEFORE(A2, ". ")

These are the bare-minimum requirements for this function to work.  However, the results are not what we need.

We don’t want all text before the first instance of a “period-space”, we want all text before the last instance of a “period-space”.

We can utilize some of the new arguments in the TEXTBEFORE function.

The [instance_num] argument allows us to define which occurrence of the delimiter to perform the extraction against.  Since we have two titles in the first user’s name, we can tell the function to split at the 2nd instance of the delimiter.

=TEXTBEFORE(A2, ". ", 2)

This works well for users with two titles but not so well for others.

Using the default of “1” for the [instance_num] argument, let’s examine the next argument: [match_mode].

=TEXTBEFORE(A2, ". ", 1)

[match_mode] allows us to define a case-sensitive or case-insensitive search for the delimiter.  Since we are searching for a “period-space” delimiter, there is no casing with which to be concerned.  The default behavior for this argument is case-sensitive (0).

=TEXTBEFORE(A2, ". ", 1, 0)

The next argument, [match_end], is an interesting bit of logic.  We can elect to “Match to end” (1) or “Don’t match to end” (0).  The default behavior is “0”.  This means that if a match for the delimiter could not be found, a “#N/A” error message will be displayed.

If we use the “1” option, the argument will match the delimiter against the end of the text.  What that means is that if the delimiter is not found, the end of the text is treated as the delimiter.  Everything to the end of the text will be returned.

=TEXTBEFORE(A2, ". ", 1, 0, 1)

The last argument, [if_not_found], allows us to display a default value, a message, or nothing if the delimiter was not found.

When not using the [match_end] option (set to “0), we see what happens with instances where the delimiter is not contained in the text.

=TEXTBEFORE(A2, ". ", 1, 0, 1, "Not Found")

Solving the Original Problem

All those new arguments are interesting, but how can we use them to solve our problem dynamically?

Forget performing some complex “count/length/search” logic.  We can easily locate the last listed title by using the [instance_num] in an unconventional way.

When defining the [instance_num] argument as a negative value, the function starts its search for the delimiter from the end of the text.

=TEXTBEFORE(A2, ". ", -1)

We can make the formula’s output more robust by using the [match_to_end] argument.

=TEXTBEFORE(A2, ". ", -1, 0, 1)

Why does this work in this manner?

Using “Martin Otto Berger” as our example, the function starts the search for the delimiter from the end of the text.  Arriving at the beginning of the text without locating an instance of the delimiter, the argument treats the entire text as the delimiter.

The TEXTBEFORE function then returns all data before the delimiter.  Since the entire text is being treated as the delimiter, everything before the text is returned, which is nothing.

💡 PROTIP: Did you notice that in examples where we got titles back, the last title was missing its “period”? That’s because we used the period to separate items, and we only got what came before it. If you want to add that period back in, just use the formula below.

=TRIM(TEXTJOIN(".", TRUE, TEXTBEFORE(A2, ". ", -1, 0, 1), " ") )

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

How to Use the TEXTAFTER Function

Extracting the names will be a bit easier to solve.  We are using the same “period-space” delimiter.

We want to extract all text after the last encountered delimiter.  We know we can use a “-1” in the [instance_num] argument to begin our search from the end of the text.

To extract all text after the last delimiter, we use the TEXTAFTER function.

The syntax for the TEXTAFTER function is as follows:

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

Using the first name in the list located in cell A1, we need to extract all text after the last occurrence of a “period-space” character set.

In the rows where there are no titles, we are seeing the “#N/A” error message.

What we want to see is the full name when no delimiters are encountered.  This can be accomplished in a couple of different ways.

One way is to display the contents of the cell being examined as the [if_not_found] argument.

=TEXTAFTER(A2, ". ", -1, 0, 0, A2)

Another way is to use the [match_to_end] argument set to “1”.

=TEXTAFTER(A2, ". ", -1, 0, 1)

The reason this works is if you recall when the delimiter is not found, the entire text is treated as a delimiter.

Because we are starting our search for the delimiter from the end of the text, when we arrive at the start of the text (having not located any instances of the delimiter), we treat everything before the text as a delimiter, returning everything after the delimiter.

Since we are at the beginning of the text, everything is returned.

It’s an interesting way to solve the issue, but it may twist your brain a bit more than just referencing the cell in the [if_not_found] argument.  Use whichever your brain likes the best.

Download the Workbook

Having more arguments to work with makes it a bit more complex, but it also gives you more ways to use it. This can help you avoid having to combine (nest) multiple functions together for more complex problems.

Feel free to Download the Workbook HERE.

Excel Download Practice file

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.