Ask Your Question

Revision history [back]

Hello

you question is a bit ambigous but I try an answer using an approach with regular expressions:

Assumption(s) for the example:

• For better readability the range of sentences is named: CLAIMS (e.g. CLAIMS is the range of cells A1:A4
• Since you consider various find strings, the pattern to be searched for is in a separate cell (in my example in B7
• The result will be into C7
• You want to find words Cat, cat, Cat, Cats, cat's, Cat's

then a (! - there might be others and smarter ones) solution may look like this:

Cell B7: \b[Cc]at[s’]{0,}\b (this is the regular expression)
Cell C7: =SUMPRODUCT(ISTEXT(REGEX(CLAIMS,B7,,"g"))*ISTEXT(REGEX(CLAIMS,B7,,"g")))

Hope that helps

Hello

you your question is a bit ambigous ambiguous but I try an answer using an approach with regular expressions:

Assumption(s) for the example:

• For better readability the range of sentences is named: CLAIMS (e.g. CLAIMS is the range of cells A1:A4
• Since you consider various find strings, the pattern to be searched for is in a separate cell (in my example in B7
• The result will be into C7
• You want to find words Cat, cat, Cat, Cats, cat's, Cat's

then a (! - there might be others and smarter ones) solution may look like this:

Cell B7: \b[Cc]at[s’]{0,}\b (this is the regular expression)
Cell C7: =SUMPRODUCT(ISTEXT(REGEX(CLAIMS,B7,,"g"))*ISTEXT(REGEX(CLAIMS,B7,,"g")))

Hope that helps