Hi - Dave here.
Happy Friday!
A few years go, I shared the formula below to remove the last word from a text string:
=MID(B5,1,FIND("~",SUBSTITUTE(B5," ","~",LEN(B5)-LEN(SUBSTITUTE(B5," ",""))))-1)
So much ugly code for such a simple task!
Thankfully, Excel now has much better tools to solve this problem. This week, I finally got a chance to update the example. The new formula now looks like this:
=TEXTBEFORE(TRIM(B5)," ",-1)
I think you'll agree that the new version is much prettier 🙂
[
Download the workbook and read the full explanation]
This is another good example of how new functions in Excel are completely changing how things are done. Often, they create much simpler solutions that take far less effort to implement. Read the article above for the full explanation, then download the workbook to try it out yourself.
Note: TEXTBEFORE requires a current version of Excel. The original formula above will work in any version of Excel and is included in the workbook.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training. The TEXTBEFORE function is covered in our
Dynamic Array Formulas course.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem with an Excel formula. You can sign up on our home page.