 # How to count words in a cell using excel functions

Share 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 😉

### 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.
5/5

– Brenda

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. ### 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.

## Related Tips

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

1. [...] Original post by Chandoo [...]

2. [...] 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, [...]

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

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

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

6. Paddydive says:

That was genious...

7. Hui... says:

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

• sarthak says:

=SUBSTITUTE(Cell,",",";",1)

only

8. 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

9. Rahim says:

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

10. ruwanal says:

every time i use it, it gives 1

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

12. Anthony Delon says:

Nice guide

13. DermaGlo says:

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

14. shravan says:

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

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

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.