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.
Where the TEXTBEFORE function extracts all text before a defined delimiter (like a comma, dash, or any other defined character or characters), the TEXTAFTER function extracts all text after a defined delimiter.
Featured Course
Power Excel Bundle
Extracting the Titles
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: You may have noticed that in each of the cases where titles were returned, the last title had its “period” removed. This is because we were using that as the delimiter and returning everything before it. If you wish to reinstate the lost period, use the formula below.
=TRIM(TEXTJOIN(".", TRUE, TEXTBEFORE(A2, ". ", -1, 0, 1), " ") )
Featured Course
Black Belt Excel Package
Extracting the Names
This problem 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.
What do you think?
Having more arguments to work with does increase the function’s complexity, but it also provides more flexibility and the reduction of cases where nesting functions would be necessary to solve more complex issues.
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Excel Essentials for the Real World
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.
