I am using the SEARCH function on a column of keywords, trying to match each cell to a list of words, and counting up how many keywords in that column have any of the words in a list. It all works fine except for one thing. If the list has a word that is short, sometimes a keyword that contains that short word within it gets counted.
For example, let's say my list of words I care about are:
  • longword
  • dia
  • others
Then my list of keywords I want to match to my word list is:
  • This is a longword
  • dials
  • dia
  • concordia
  • dia alone
  • just dia
  • others
  • blah

So I only want to count the cells that have "dia" as its own word (in bold), not "dials" or "concordia." If this were a regular expression match, it would be easy. But how do I specify to Excel in a Search function that I only want these keywords when they match what's in the list only when it's got a space or beginning/end of line on either side?

BTW, I am using the function =IF(SUMPRODUCT(--(ISNUMBER(SEARCH(listofwords,$A7))))>0,"found","") to do the matching, then have a COUNTIF at the bottom of the column to add up the cells with "found" in them.

Thanks!