How to count words in a cell using excel functions

I was doing some weird analysis on corporate mission statements and I had to count the number of words in each cell. That is when I realized there is no formula to calculate the number of words in a cell, I was too lazy to write an UDF for that, so I figured out a nearly perfect way to calculate no. of words in a cell using existing formulas.

If you want to calculate the no. of words in cell a1, then use the formula:
=len(a1)-len(substitute(a1," ",""))+1

What this does is, it calculates the number of spaces in a cell and adds 1 to it, thus almost finding the number of words in a cell. I say almost because, if a cell has “this blog rocks,really!”, this formula will calculate the words as 3, where as there are 4 words in there

But that is for you to think

Also: Concatenate a bunch of cells using simple formula, Generate tag clouds in excel using vba, Master your IFs and BUTs

Posts & Navigation Have an Excel Question? « Photographic Fridays #3 – Daisies Partition Charts in Excel – alternative to pie charts [visualization hack] » Go Back to Home Page Tags: Analytics, count, howto, ideas, learn, Learn Excel, microsoft, Microsoft Excel Formulas, tricks

Comments

RSS feed for comments on this post. TrackBack URI

Leave a comment

If you have a question, please ask in the forums