# Count no. of occurrences of words or phrases in columns even if there are multiple phrases or words in one cell

#### jeremypyle

##### Board Regular
Hi,

I want to count the number of occurrences of words or phrases in columns even if there are multiple phrases or words in one cell.

Suppose I have the following:
a1=red cow
a2=blue cow
a3=red cow
a4=yellow cow, red cow
a5=blue cow
a6=red cow

Now I want a formula that will result in the following:
b1=red cow4
b2=blue cow2
b3=yellow cow1
b4=blue cow1

I will have several hundred thousand items in column A so I need a formula that I can simply drag down in column B that will show the number of the occurence of each phrase.

Is it also possible to make a formula that will do something similar except over multiple columns?

For example:
a1=red cow
a2=blue cow
a3=red cow
a4=yellow cow, red cow
a5=blue cow
a6=red cow

b1=black cow
b2=blue cow
b3=red cow
b4=pink cow, red cow
b5=orange cow

c1=pink cow
c2=milking cow
c3=purple cow
c4=black cow
c5=blue cow, red cow
c6=red cow
c7=white cow

Then in d1, I need the formula that will result in the following:
d1=red cow8
d2=blue cow4
d3=yellow cow1
d4=black cow2
d5=pink cow2
d6=orange cow1
d7=milking cow1
d8=purple cow1
d9=white cow1

I'm stuck so would greatly appreciate anybody's help on this. Thanks so much

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Are they always separated by commas?

I can only come up with a formula to find it if it occurs once in the cell in the whole range (if you are doing this for 200k+ cells, you are going to have a long calculation time, so be warned):

=COUNT(SEARCH("RED COW","\$A1:\$A200000,1))
entered with CTRL-SHIFT-ENTER
this would find how many times red cow appeared in the range A1:A200000, for instance (you could do this to A1:Z200000 and get the same results - to answer the multi-column part. I could probably fashion a solution for if the cell had it more than once, but its not coming to me off the top of my head...

 Data Value in Cell Total Entries (A1) red cow (B1) Yellow Cow =COUNTIF(A\$1:A\$10, "*"&B1&"*") yellow cow, red cow Red Cow red cow Blue Cow milk cow blue cow red cow red cow milk cow blue cow red cow

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Drag code in column C down as necessary. This would work for multiple columns as well, just change A\$10 to the end of your data.

The "*"&B1&"*" code will put a wildcard before and after whatever is B1.
So, if B1 is Yellow Cow, and anywhere in the data it said 8233yellow cow23432, it would find it and count it.

Another way to separate out the commas in the data, which I would highly recommend, is using the Text-to-Columns tool under the Data tab, with a comma as the delimiter.

Are they always separated by commas?
Yes they are

I like Piktro's answer as its a little less intensive than mine.. That said, I also determined how to count the first AND second occurence of the variable in a cell:

=COUNT(SEARCH("RED COW",A1:A200000,1))+COUNT(SEARCH("RED COW",A1:A200000,LEN("RED COW")+SEARCH("RED COW",A1:A200000,1)))

You could add successive loops of the search + search + len to count the next Nth occurrence as well!
Yes, I had to one up you this time

Thanks Piktro and S Hal. That helps so much!!! So grateful for your help =)

Replies
3
Views
602
Replies
4
Views
140
Replies
1
Views
101
Replies
6
Views
279
Replies
0
Views
121

1,196,359
Messages
6,014,798
Members
441,847
Latest member
hw407

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