Search string with multiple criteria
 

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("*he lie*",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.

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


"fLiPMoD£" wrote:

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("*he lie*",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.

fLiPMoD=A3 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

=3DIF(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,

=3DIF(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:
=3DIF(AND(SUBSTITUTE(Q5,{"HELIE","PUSWJ","Jersey"} ,"")=3DQ5),"Not
","")&"Jersey"