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


Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Critical Path with Excel

Calculating Critical Path using Excel Formulas [Project Management]

Do you know that we can easily calculate the critical path for a project using Excel formulas?

For a long time, it has been tricky to calculate the Critical Path using Excel formulas. But thanks to the arrival of new Dynamic Array functionality in Excel, we can now calculate critical path. In this article let me describe the approach with an example.

Put on your hardhats, this one is going to blow your minds.

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