Close this search box.

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.

Announcing Power BI Dashboard Contest 2024

Announcing Power BI Dashboard Contest (win $500 prizes!)

Hey there, I have a SUPER exciting announcement! April is about to get a whole lot sweeter with our Power BI Dashboard Contest! Your mission, should you choose to accept it: Craft the most EPIC dashboard for the Awesome Chocolates CEO with sales & financial insights! Winners stand a chance to score up to $500 in Amazon Gift Cards, plus some serious bragging rights!

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