Hi - Dave here.
Happy Friday, and Happy New Year!
I'm still working on a project to update many of my older formulas to use new functions in Excel.
Below is another interesting example of an older more complicated formula being replaced by a simple formula that works just like you'd expect it to.
The old version of the formula looks like this:
=LEN(TRIM(B5))-LEN(SUBSTITUTE(B5," ",""))+1
The new version of the formula (below) looks like this:
=COUNTA(TEXTSPLIT(TRIM(B5)," "))
[ Download the workbook and read the full explanation]
The old formula is an indirect approach that involves counting words by counting spaces:
- Clean up the text with TRIM
- Count characters in the result with LEN
- Remove all space characters with SUBSTITUTE
- Count characters in the result with LEN
- Subtract the second count from the first count
- Add 1 to get a final word count
Ugh. This works because the word count is equal to the number of spaces + 1, but it's a messy and non-intuitive process.
The new formula contains 3 simple and logical steps:
- Clean up the text with TRIM
- Create a list of words with TEXTSPLIT
- Count the words in the list with COUNTA
This is so much better! Not only is the formula shorter, but the steps just make sense. Bravo to the Excel team for getting new functions like TEXTSPLIT into Excel. They are game changers. The article above contains both formulas and a full explanation. Download the workbook and try it out for yourself.
Excel formulas
We maintain a list of over 500 working formulas here.
If you need more structure, we also offer video training.
Have a great weekend!
Dave |
