Among the 14 new functions added to Excel 365 (version 2208), the function TEXTBEFORE and TEXTAFTER are crucial to help to extract sub-string on a delimiter (space, comma, dashes, ...).
Let's extract from this document the names, first names, cities, zip codes, and addresses.
Extract the first word of a text
The TEXTBEFORE function allows you to extract the first word of a text based on a delimiter, such as space.
Extract the first word from a text
To extract the first word from a string, simply write the following function
=TEXTBEFORE(A2," ")
The function is easy to understand; "We extract the first word BEFORE the first delimiter."
Extract the first two words
But the TEXTBEFORE has a third interesting argument that allows you to indicate the number of words (or occurrences) we want to return.
To extract the name and surname, we will write the following formula
=TEXTBEFORE(A2," ",2)
To get the same result with the LEFT and SEARCH functions, one would have had to write
=LEFT(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1))
The TEXTAFTER function
Conversely, the TEXTAFTER function will return the rest of the characters' string from the nth delimiter.
So, to return the address, postal code, and city, we will write:
=TEXTAFTER(A2," ",2)
Extract the last 2 words
But what is much more enjoyable with the TEXTAFTER function it's to put a negative number in the 3rd argument.
Indeed, by putting -1, you extract the last word, and by putting -2, you extract the last 2 words (easy 😀). In this situation, the delimiter is a comma
=TEXTAFTER(A2,",",-2)
I tried to build you an equivalent formula with the RIGHT function but finding the penultimate space is impossible.
So this new function enriches the possibilities of text extraction in Excel 😀👍
How to extract a subchain in the middle?
We have seen how to extract the first name, first name, and last name, but how to extract only the name? You must make double use of these formulas to obtain this result.
- A first time extracting the first 2 words
- And we will deduce from this result the last word
=TEXTAFTER(TEXTBEFORE(A2," ",2)," ")
And finally, to extract the address only, we will
- Reduce the initial text of the first and last name on the space
- From this result, we remove the last 2 words on the comma
=TEXTBEFORE(TEXTAFTER(A2," ",2),",",-2)
