Search string with multiple criteria

[fLiPMoD£]

Hi
I'm trying to write a formula that does the following. If the text string in
column Q contains any of the following text strings "HELIE","PUSWJ" or
"Jersey" then return a text string of "jersey" in coumn AH.

When i try

=IF(OR(SEARCH("*jersey*",Q:Q), SEARCH("*puswj*",Q:Q),SEARCH("*helie*",Q:Q)),
"Jersey", "Not Jersey")

I get an error #value
...............
However when i tried this,

=IF(SEARCH("*jersey*",Q:Q),"Jersey",IF(SEARCH("*helie*",Q:Q),"Jersey",IF(SEA
RCH("*PUSWJ*",Q:Q),"Jersey","Not Jersey")))

I only get a result if jersey is in the string. The other are not picked up
by the above formula.

Thank you all very much in advance.

......Coming from Where I'm From.

 

[Guest]

here are 2 ways

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH({"jersey","puswj"},Q1:Q100))))>0,"Jersey","Not jersey")

entered normally

=IF(OR(ISNUMBER(SEARCH({"jersey","puswj"},Q1:Q100))),"Jersey","Not jersey")

entered with ctrl + shift & enter


Regards,

Peo Sjoblom

 

[Harlan Grove]

fLiPMoD£ wrote...

I'm trying to write a formula that does the following. If the text string in
column Q contains any of the following text strings "HELIE","PUSWJ" or
"Jersey" then return a text string of "jersey" in coumn AH.

When i try

=IF(OR(SEARCH("*jersey*",Q:Q), SEARCH("*puswj*",Q:Q),SEARCH("*helie*",Q:Q)),
"Jersey", "Not Jersey")

I get an error #value

First, *BAD* idea to use entire column references.

Second, no need to include the '*' wildcards.

Unless the cell in question contains *ALL* of these substrings, one of
the SEARCH calls will return #VALUE!, in which case OR will return
#VALUE!, and so will IF.

However when i tried this,

=IF(SEARCH("*jersey*",Q:Q),"Jersey",IF(SEARCH("*helie*",Q:Q),"Jersey",
IF(SEARCH("*PUSWJ*",Q:Q),"Jersey","Not Jersey")))

I only get a result if jersey is in the string. The other are not picked up
by the above formula.

Others not picked up means this formula would also return #VALUE!?

Try

x5:
=IF(AND(SUBSTITUTE(Q5,{"HELIE","PUSWJ","Jersey"},"")=Q5),"Not
","")&"Jersey"