Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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