Finding a list in a string

[awstewar]

Hello,

I was wondering if there is a function that can a take a list and se
if any of those words exist in one cell. Like VLookup, but no
matching exactly.

Thank

 

[Kostis Vezerides]

Hi

Assume your words are in range A1:A10. Assume cell is B1.
Then the following ARRAY formula will do:

=SUM(IF(ISERROR(FIND(A1:A10, B1)),0,1))
(Shift+Ctrl+Enter)

The number returned will be the number of words in A1:A10
found in B1.

HTH
Kostis Vezerides

 

[Aladin Akyurek]

=1-ISNA(LOOKUP(2,1/SEARCH(E1:F1,A2)))

where E1:F1 houses a list of substrings of interest and A2 a target string.

1 as result means: one or both of the subtrings occurs in the string in A2
and 0 the opposite.

Caveat. If E1:F1 is empty, the result is necessarily 1.