Forum Discussion
How to match phrases found in text?
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. | ok | a phrase |
| Found a phrase that should match. | review | N/A |
3 Replies
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. 🙂
Not really an elegant way.
You have to substitute the punctuation with a space.
=SUM(--ISNUMBER(SEARCH(" "&$E$2:$E$5&" "," "&SUBSTITUTE(A2,"."," ")&" ")))=0
