Count multiple instances of keywords in a cell

miruku - Feb 4, 2016 at 03:34 AM
I have to scan through a column of text comments for certain keywords, and count the number of times these keywords pop up in each comment.

For example, I need to look for the number of times the words "apple","orange" and "pear" appear in cell A1. Cell B1 should display the desired answer.

Cell A1: I bought apples and oranges.
Cell B1 <Number of times keywords appear>: 2

I am able to solve this using =SUM(COUNTIF(A1,"*apple*")+COUNTIF(A1,"*orange*")+COUNTIF(A1,"*pear*"))

My problem occurs with the second type of comment.

Cell A2: I bought one red apple, two green apples and some pears.
Cell B2 <Number of times keywords appear>: 3

COUNTIF will only count the first "apple" and give the result 2, which is incorrect.

This would work with =SUM(

This count all the "apples" in the cell. My issue with it is that the complete formula will be far too long as I have over 50 keywords (and I doubt Excel would allow it). Is there a shorter formula that will resolve this problem? Perhaps one that references a keyword table? (although I don't know how to achieve this)

Thank you!