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
16 Responses
That was genious…
@Paddydive
You can extend the formula to include ,’s and ;’s etc by:
=LEN(A1)-LEN( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1, " ",""), ",", ""), ";", ""), " ", "")) +1=SUBSTITUTE(Cell,”,”,”;”,1)
only
This can also be achieved using a UDF. That would be much handy. I tried counting words in a cell using the following UDF.
Function CountWords(txt)
x = Len(Trim(txt)) – Len(Replace(Trim(txt), ” “, “”)) + 1
CountWords = x
End Function
=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,” “,””),”,”,””))+1
every time i use it, it gives 1
Nice guide
Excellent, what a web site it is! This weblog presents helpful
information to us, keep it up.
how to remove text in cell
for eg. in cell text is (xxccjjkk1234576hdjdj) & require only numbers 1234576.