Hello,
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(
(LEN(A10)-LEN(SUBSTITUTE((UPPER(A10)),UPPER("apple"),"")))/LEN("apple")+
(LEN(A10)-LEN(SUBSTITUTE((UPPER(A10)),UPPER("pear"),"")))/LEN("pear"))
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)
