Counting multiple words in cell

[trumi]

Hi, I'm trying to count multiple occurrences of words in a cell.
Essentially, I would like to count the occurrence of the following words: apple, orange, banana, strawberry
so that for the data in the below table I get the following values

Data	Count of words
apple, orange, tree, butter	2
banana, orange, strawberry	3
motorcycle, dog	0
cat, apple,	1
grass, banana, orange	2

I first thought this was a job for the

COUNTIFS

function, with something like

=COUNTIFS(A2,"*apple*",A2,"*orange*",A2,"*strawberry*",A2,"*banana*")

, but unfortunately, it seems it only counts cells, not occurrences of words within cells.
I would like to count all of the words in my list, not just whether or not any of the words appears in the cell.
Any idea how to accomplish this?

 

[rondeondo]

if it is just those specific 4 words you're looking for you could use find something like:
=if(isnumber(find("apple",a2)),1,0)+if(isnumber(find("orange",a2)),1,0)+if(isnumber(find("strawberry",a2)),1,0)+if(isnumber(find("banana",a2)),1,0)
but if there's a chance for one word to appear multiple times you may need to resort to VBA

 

[trumi]

if it is just those specific 4 words you're looking for you could use find something like:
=if(isnumber(find("apple",a2)),1,0)+if(isnumber(find("orange",a2)),1,0)+if(isnumber(find("strawberry",a2)),1,0)+if(isnumber(find("banana",a2)),1,0)
but if there's a chance for one word to appear multiple times you may need to resort to VBA

It worked! Thank you so so so so much! You got me out of such a bind.

 

[Zot]

Your sample shows that you want to count fruits. You can use UDF.

Option Compare Text

Function CountFruits(cell As Range) As Long

Dim ArryItems() As String

ArryItems = Split(cell.Value, ",")
CountFruits = 0

For n = 0 To UBound(ArryItems)
    Select Case Trim(ArryItems(n))
        Case "Orange", "Apple", "Banana", "Strawberry"
            CountFruits = CountFruits + 1
    End Select
Next
    
End Function

 

[rondeondo]

actually if you wanted to count each word multiple times you can do that without VBA...
using =(len(a2)-len(substitute(a2,"apple"))/len("apple") will tell you how many times "apple" is in A2. repeat for the other words...

 

[sherif ashraf]

actually if you wanted to count each word multiple times you can do that without VBA...
using =(len(a2)-len(substitute(a2,"apple"))/len("apple") will tell you how many times "apple" is in A2. repeat for the other words...

hello,
does this mean i can add + sign and then repeat the same formula with the other word i want, plus i wanted to know if im importing from a different tab same sheet would that be a problem ?

 

[rondeondo]

Hi Sherif
if you mean this:

=(len(a2)-len(substitute(a2,"apple"))/len("apple") + (len(a2)-len(substitute(a2,"banana"))/len("banana")

then yes, but the result of that would be how many times apple and banana appear, collectively.
depends what you want to use it for as to if that will work.
if you have a number of words you want to look for you can put each word as a column header and replace the string with the cell ref. Then you'll get a matrix with a number for the count of each word.
If doing this, remember to use absolute row ref and relative column ref, then the formula can be copied through the area.

ie, if your words to count are in B1 through to Z1

=(len($a2)-len(substitute($a2,b$1))/len(b$1)

Copy this through b2 to z100 (how ever many row you have) and it will refer to Col A for your descriptions and the header row for each checked word.
I've used something similar with find. works nicely.