# Count cells with more than 4 words in the cell

#### jrake40

##### New Member
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?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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:
Code:
``=SUM(IFERROR(SEARCH(" ",MID(TRIM(A1),ROW(INDIRECT("1:"&LEN(TRIM(A1)))),1)),0))+1``

or maybe this, B1:

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

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

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

Replies
1
Views
39
Replies
12
Views
137
Replies
5
Views
62
Replies
8
Views
111
Replies
3
Views
151

1,203,240
Messages
6,054,315
Members
444,716
Latest member
choumnan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back