Count cells with more than 4 words in the cell

[jrake40]

Looking for a way to count all the cells in Column A of my spreadsheet that have more than 4 words in the cell. The cell text is variable. Is this possible with VBA?

 

[CyrusTheVirus]

You could probably do that with a formula, but you should post a sample of your data so we can see what it looks like.

Are all words separated by a space?

You might be able to do it with something like the below formula? Enter it with Ctrl+Shift+Enter, not just Enter.

B1:

=SUM(IFERROR(SEARCH(" ",MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1)))),1)),0))+1

or maybe this, B1:

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

 

[CyrusTheVirus]

Sorry, I overlooked that you wanted over 4, then try the below formula, just press Enter to enter the formula.

=IF((LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)>4,"Yes","No")