Get the TEXTBEFORE & TEXTAFTER Practice Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with every method in this tutorial.
- Practice extracting text before and after a delimiter with real examples
- See how to pull text before or after the last delimiter in a cell
- Includes name and title examples already set up for testing
Enter your email below for instant access.
How to Extract Text Before or After a Delimiter
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.
TEXTBEFORE Function: Syntax and Examples
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), " ") )
TEXTAFTER Function: Syntax and Examples
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.
TEXTBEFORE and TEXTAFTER split text apart. If you need to do the opposite and join columns together, see our guide on how to combine two columns in Excel.
Download the Free Practice File
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
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
