The second formula is significantly more complex and only makes sense if you don’t have TEXTBEFORE.
This formula is a great example of hownew functionsin Excel completely change how problems are solved.
The traditional formula is much more complex than the modern formula.
This argument is optional and defaults to 1.
This is why we get “Bob” when we don’t provide a value forinstance_num.
TRIM makes sure there is just one space between words and removes any leading or trailing spaces.
The “trimmed” text is returned directly to TEXTBEFORE as thetextargument.
The TEXTBEFORE function then returns all text before the last word, effectively removing the last word.
We can also easily change the delimiter used to separate words.
The challenge is to figure out where the last word begins and set a marker before the last word.
Then, we can locate the marker with FIND and extract everything up to that point.
The formula is convoluted, but the steps are simple.
you could use any character you like, so long as it doesn’t appear in the source text.
The result, 33, is returned to the MID function as thenum_charsargument.
We are finally ready to extract text.
This formula is a great example of hownew functionsin Excel completely change how tricky problems are solved.
The traditional formula is much more complex and much less transparent than the TEXTBEFORE solution.
Customizing the traditional formula
The same formula structure can be used with a different delimiter.
For example, =MID(“apple”,2,3) returns “ppl”.
When the text is not found, FIND returns a #VALUE error.
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching.
LEN will also count characters in numbers, but number formatting is not included.
Related videos
Excel TEXTBEFORE function
