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"
|