TEXTBEFORE and TEXTAFTER are new Excel 365 functions that allow you to extract text parts easily. These functions are easy to use and return complex extraction like what FlashFill can do.
They can efficiently extract names, first names, cities, zip codes, and addresses from text.
Extract the first word of a string.
The TEXTBEFORE function returns text that occurs before a given character or string, like a space.
Extract the first word from a text
To extract the first word, write =TEXTBEFORE(A2," ").
It is simple: you extract the text BEFORE the first delimiter.
Extract the first two words
TEXTBEFORE has a third argument that specifies how many words to extract.
To extract the name and surname, use =TEXTBEFORE(A2," ",2).
Using LEFT and SEARCH, you would need a complex formula. So TEXTBEFORE is straightforward to use 😉
=LEFT(A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1))
The TEXTAFTER function
The TEXTAFTER function extracts text after the nth delimiter.
To return the address, postal code, and city, we must extract the string after the second “space”. So the formula is =TEXTAFTER(A2," ",2).
Use a negative value
TEXTAFTER and TEXTBEFORE allow negative values, and this is super useful. A negative value means you read your string from the right 😮👍
For instance, to extract the City name, that means the word after the last comma of the string, we will write the formula : TEXTAFTER(A2,",",-1)
How do you extract a substring in the middle?
To extract only the name, you combine both functions.
- First, TEXTBEFORE extracts the first two words.
- Then, TEXTAFTER extracts the last word from that result.
=TEXTAFTER(TEXTBEFORE(A2,” “,2),” “)
Another example is how to extract the address only. You can do it with these steps.
- Remove the name and surname using a space delimiter.
- Remove the last two words using a comma delimiter.
=TEXTBEFORE(TEXTAFTER(A2,” “,2),”,”,-2)
