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 Bundle
Power Excel Bundle
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 Bundle
Black Belt Excel Bundle
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.
Featured Course
Excel Essentials for the Real World
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
