How to check if any one of the large group of strings are present in cell.

[cool_anu4u]

Hello,

I'm trying to check if any one of the selected words are used in the target cell.

Example I want to check for the words - "ST", "RAM", "DOG", "SING"......(around 100 words). These strings are arranged in column A:A

In a column B:B, I 've a series of strings . I want to check if any of the words from column A are used in column B. Column B has over 10000 rows. Ex : if a string in column B says I LOVE MY DOG, then it should return a true value as "DOG" is present in column A.

I'e tried using =OR(NOT(ISERROR(FIND({"ST","DOG","SING","RAM"......},B:B))))..but the formula is too long and impractical , Also , I'm not getting the correct value. Can anyone suggest me an alternative. ??

Thanks in advance. !!

[XOR LX]

Hi,

Assuming that your list of search words is in A2:A100 (and that this list contains no blanks), and that the list for which you wish to check if any of these words is present begins in B2, enter this in C2 and copy down as required:

=SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$100,B2)))>0

Regards

[cool_anu4u]

Thanks for the prompt reply. I've tried, but the solution works partially. The formula does not take spaces or entire word in to account. Say for example I need check for "sing" or "ave", it returns TRUE for words such as "singer" and "avenue", which have common characters. Do we have a work around for this. ?

[XOR LX]

Hi,

What other punctuation marks may be in the strings in column B? You don't give any examples of these strings in your original post.

If there are none, and so effectively each string consists of words separated by single spaces, then we can be sure that a simple amendment viz:

=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$A$2:$A$100&" ",B2)))>0

will work.

If not, then get back to me with perhaps a few examples of the type of strings we're dealing with and we can look at workarounds.

Regards

[cool_anu4u]

I've tried the suggestion, but its not working...I've added spaces, the result is all FALSE..

Here's an example:
column A column B column C formula

1. ROY 42nd AVENUE STREET FALSE- <=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$A$1:$A$4&" ",B1)))>0>
2. DEN TOP - KIT DENTIST FALSE
3. RAM I LOVE KITE FALSE
4. AVE MY NAME IS ROY FALSE

Other than spaces there are few random numbers and "-" characters. Can't think of a pattern.

[cool_anu4u]

Please read it as

A has ROY, DEN, RAM, and AVE

B has

42ND AVENUE STREET
TOP - KIT DENTIST
I LOVE KITE
MY NAME IS ROY

C <OUTPUT>
FALSE
FALSE
FALSE
FALSE

[benishiryo]

welcome to the forum, cool_anu4u. can't you post your sample excel file without confidential data & let us do the pattern analyzing? otherwise, you would keep coming up with problems. for SUMPRODUCT, you can add the red portion:
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&$A$2:$A$100&" "," "&B2&" ")))>0

or you can use:
=ISNUMBER(LOOKUP(2^15,SEARCH(" "&$A$2:$A$100&" "," "&B2&" ")))

so if "Dog" is under column A & you have these in column B:
Junior-Dog
Junior is a Dog.

both are supposed to read TRUE? if so, you can either range up those in column A which are not blanks like this:

=ISNUMBER(LOOKUP(2^15,SEARCH(" "&$A$2:$A$6&" "," "&SUBSTITUTE(SUBSTITUTE(B2,".",""),"-"," ")&" ")))

or do a dynamic range:

=ISNUMBER(LOOKUP(2^15,SEARCH(" "&$A$2:INDEX(A:A,COUNTA(A:A))&" "," "&SUBSTITUTE(SUBSTITUTE(B2,".",""),"-"," ")&" ")))

A2 is where i start. COUNTA counts the number of filled cells in column A. say you start in A4 & there is a header in A3 & nothing in A1:A2, then it's COUNTA(A:A)+2

[XOR LX]

Hi again,

Can you try this array formula (please make sure you know how to enter this type of formula in Excel) in C1 and copy down? I've kept the list of search words as A1:A4 as in your previous example.

=SUM((ISNUMBER(SEARCH($A$1:$A$4," "&B1&" "))*(IFERROR(CODE(MID(LOWER(" "&B1&" "),SEARCH($A$1:$A$4," "&B1&" ")-1,1)),0)<97))*(IFERROR(CODE(MID(LOWER(" "&B1&" "),SEARCH($A$1:$A$4," "&B1&" ")+LEN($A$1:$A$4),1)),0)<97))>0

Let me know if the results are what you would expect and any issues.

Regards