 # 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

Chandoo is an awesome teacher
5/5

– Jason

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.

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