SOLVED

How to match phrases found in text?

Copper Contributor

The goal is to use a named range of the keywords/phrases to flag sentences in column A that have any of them in column B.  I have not had luck with SEARCH or MATCH and would rather not use a macro or extension that other team members would have to install.  Examples I have seen conveniently don't deal with substrings that also match (e.g. "and" shouldn't match "command").

 

I'm hoping I'm just missing a built-in function that I'm not familiar with yet.


Thank you!  This has been a head-scratcher.

 

A (sentences)B (results)C (keywords/phrases)
The dog was commanded to sit.ok (not a match 'and')and
This and that.review...
This is not a match.oka phrase
Found a phrase that should match.reviewN/A
3 Replies
best response confirmed by DrPepperFreak (Copper Contributor)
Solution

@DrPepperFreak 

SEARCH() is the way to go.

=SUM(--ISNUMBER(SEARCH(" "&$E$2:$E$5&" "," "&A2&" ")))=0

 

@Detlef Lewin, thank you for taking the time to help. Ater taking a few minutes to figure out the trick and look up "--", this solution is AWESOME. I think it covers 99% of the cases.; however, I noticed that if one of the words or phrases happens to be adjacent to punctuation, it doesn't count it as a match (new requirement, huh?) Is there an equally elegant way to also deal with punctuation possibilities? Thank you for getting me 99% there. :)

@DrPepperFreak 

Not really an elegant way.

You have to substitute the punctuation with a space.

=SUM(--ISNUMBER(SEARCH(" "&$E$2:$E$5&" "," "&SUBSTITUTE(A2,"."," ")&" ")))=0

 

1 best response

Accepted Solutions
best response confirmed by DrPepperFreak (Copper Contributor)
Solution

@DrPepperFreak 

SEARCH() is the way to go.

=SUM(--ISNUMBER(SEARCH(" "&$E$2:$E$5&" "," "&A2&" ")))=0

 

View solution in original post