help! comparing text for common words

C

cloudy682

Hi, here is what I want to do.

I have a list of 4000 strings that look like this:

In cell A1:
AUS,AUT,BEL,DEN,FIN,FRA,GER,GRE,HKG,IRE,ITA,KOR,NET,NOR,NZE,POR,SIN,SPA,SWE,SWI,TAI,UKI

In cell A2:
AUS,BRA,CAN,HKG,ISR,JPN,KOR,MEX,NZE,SAF,SIN,TAI,USA

I have another string with the specific words I'm looking for, such a
BEL,FIN,ZIM

I want to mark every string from A1 to A4000 that have words that matc
any of the words i'm looking for.

In my example, the string in A1 will be marked because it has BEL an
FIN, and A2 will not because it doesn't have BEL, FIN , or ZIM.

How can I find a way to do this quickly and efficiently?
Thanks in advanc
 
I

immanuel

Use the following formula in a blank cell on Row 1:

=MAX(IF(ISERROR(SEARCH({"BEL";"FIN";"ZIM"},A1)),0,1))

And drag down to the end of your range.

/i.
 
I

immanuel

In my haste to post, I forgot to mention that the formula will return a 1 or
0 depending on whether any of the search terms are found in the target cell.

So, if you'd prefer a true or a false, you can use it like so:

=MAX(IF(ISERROR(SEARCH({"BEL";"FIN";"ZIM"},A1)),0,1))=1

You might even choose to use the above formula as a conditional formatting
formula.

Either way, I hope it helps.

/i.

immanuel said:
Use the following formula in a blank cell on Row 1:

=MAX(IF(ISERROR(SEARCH({"BEL";"FIN";"ZIM"},A1)),0,1))

And drag down to the end of your range.

/i.

cloudy682 said:
Hi, here is what I want to do.

I have a list of 4000 strings that look like this:

In cell A1:
Click to expand...
AUS,AUT,BEL,DEN,FIN,FRA,GER,GRE,HKG,IRE,ITA,KOR,NET,NOR,NZE,POR,SIN,SPA,SWE,
Click to expand...
 
Top