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
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:

Leave a Reply
« Photographic Fridays #3 – Daisies  Partition Charts in Excel – alternative to pie charts [visualization hack] » 
16 Responses to “How to count words in a cell using excel functions”
[...] Original post by Chandoo [...]
[...] see: How to count words in an excel cell, Fill only weekdays when autofilling dates, Find unique items in a list Tags: data, excel, [...]
[...] on names and text formulas: Find word count using excel formulas, 15 excel formulas for everyone, Generate tag clouds using VBA. Categories : Excel Tips  [...]
[...]  bookmarked by 5 members originally found by garpo on 20080919 How to count words in a cell using excel functions http://chandoo.org/wp/2008/07/08/countwordsexcelformula/  bookmarked by 1 members originally [...]
[...] of words in a cell, use =len(trim(text))len(SUBSTITUTE(trim(text),” “,”"))… Get Full Tip 32. To count positive values in a range, use =countif(range,”>0″)… Get Full Tip [...]
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
[…] give the credit of my learning about this formula, I must ask you to visit MS Excel expert’s website as it can help you to learn even more formulas that you may need for another purpose […]
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.
[…] I must thank to Chandoo.org from where I learn how to count words in a cell using excel functions and also many other excel tricks like […]