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:

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

16 Responses to “How to count words in a cell using excel functions”

  1. [...] see: How to count words in an excel cell, Fill only weekdays when auto-filling dates, Find unique items in a list Tags: data, excel, [...]

  2. [...] on names and text formulas: Find word count using excel formulas, 15 excel formulas for everyone, Generate tag clouds using VBA. Categories : Excel Tips | [...]

  3. [...] - bookmarked by 5 members originally found by garpo on 2008-09-19 How to count words in a cell using excel functions http://chandoo.org/wp/2008/07/08/count-words-excel-formula/ - bookmarked by 1 members originally [...]

  4. [...] 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 [...]

  5. Paddydive says:

    That was genious...

  6. Hui... says:

    You can extend the formula to include ,'s and ;'s etc by:
    =LEN(A1)-LEN( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1, " ",""), ",", ""), ";", ""), " ", "")) +1

  7. Chanda Kaushik says:

    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

  8. Rahim says:

    =LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1," ",""),",",""))+1

  9. ruwanal says:

    every time i use it, it gives 1

  10. […] 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 […]

  11. Anthony Delon says:

    Nice guide

  12. DermaGlo says:

    Excellent, what a web site it is! This weblog presents helpful
    information to us, keep it up.

  13. shravan says:

    how to remove text in cell
    for eg. in cell text is (xxccjjkk1234576hdjdj) & require only numbers 1234576.

  14. […] 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 […]

Leave a Reply

« »