LinkBack URL
About LinkBacks
I want to count the number of cells in a column that contain 3 or more words in them. Is there any formula to do that? Let's say the range is from A3 to A21.
How to count the number of cells that contain a number of words?
I want to count the number of cells in a column that contain 3 or more words in them. Is there any formula to do that? Let's say the range is from A3 to A21.
Try this formula in B3
Copy and paste in B3 and drag it down
=LEN(A3)-LEN(SUBSTITUTE(A3," ",""))+1
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
or if you want it in a single cell.. from Alkey len()
=SUMPRODUCT(--(LEN(A3:A21)-LEN(SUBSTITUTE(A3:A21," ",""))+1>=3))
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
More robust is:
=SUMPRODUCT(--(LEN(TRIM(A3:A21))-LEN(SUBSTITUTE(TRIM(A3:A21)," ",""))>1))
Just in case there are leading/trailing spaces and/or repeated spaces.
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Worked perfectly, Thanks so much
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks