I want to test the text in each cell in a column (C), whether it contains any of the words in a list (Ref.$D$3:$D$4)
See attached file:
Currently using following formula, but it doesn’t lend itself to adding words to the list:
=IF(AND(ISERROR(FIND(Ref.$D$3,C5,1)), ISERROR(FIND(Ref.$D$4,C5,1))),"",D5)
Tried the MATCH function but it doesn’t return expected results:
=MATCH(C5,Ref.$D$3:$D$5,1)
Looking for a better way to accomplish this.
Please attach a sample file, (edit your question to add it).
The attached solution uses the REGEX() function available in LibO V6.2 or higher.
Always name your version.
Thank you so much for you answer.
I never would have thought to use REGEX()
I’m amazed at how there’s nearly always at clever way to accomplish a task.
Thanks again!
Playing around with it, one hiccup I found was:
If the word in the test text is not delimited by a space (before (not 1st word) and after), it is not detected.
This seems to run counter to the way the “/b” regular expression is suppose to work.
\b (not /b!) in a regular expression means a word boundary.. Since you wanted to look for words this zero-length part simply is needed for the functionality.
Otherwise you wouldn’t find words but substrings. See See: Regex Tutorial - \b Word Boundaries
This isn’t hair-splitting at all, but very relevant for a related functionality in many cases.
Using a RegEx in a similar case without the word boundaries, you would find sex in Essex, and cat, cater, pill, ill in caterpillar. Seeing it case-insensitive, the caterpillar would even contain the ERP (European Recovery Programme).
Searching for arnbitrary substrings, on the other hand, you can’t use REGEX() anyway, because many of the expactable characters have a special meaning in RegEx and cannot be searched as literals without a specific escape construct.
The alternative woiuld be -for the logical result- NOT(ISERROR(FIND(SubString; TargetString))).