IF formula with multiple data range in cell
 

I'm trying to create an IF formula that returns data after searching a cell
that contains more than one data range. For ex:

Cell B3 contains GCVW-00001, GCVW-00002
I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I need
the formula in D3 to return Yes if B3 contains GCVW-00002

I'm able to get the formula to return yes or no correctly if cell B3
contains either GCVW-00001 or GCVW-00002, but the formula won't work if it
contains both at the same time.

right now my basic formula looks like:
=IF(B3="GCVW-00001","Yes","No") or =IF(B3="GCVW-00002","Yes","No")

I'm thinking the problem is with the = portion. Is there a way to do Conains
instead of equals?

thanks :)

IF formula with multiple data range in cell
 

This will return Yes if B3 hold GCVW-00001 no matter what else is in the
cell
=IF(LEN(B3)<LEN(SUBSTITUTE(B3,"GCVW-00001","")),"Yes" ,"No")
And if you can cope with simple TRUE or FALSE, this is simpler
=LEN(B3)<LEN(SUBSTITUTE(B3,"GCVW-00001",""))

best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"stumped" wrote in message
...
I'm trying to create an IF formula that returns data after searching a
cell
that contains more than one data range. For ex:

Cell B3 contains GCVW-00001, GCVW-00002
I need the formula in C3 to return Yes if B3 contains GCVW-00001 and I
need
the formula in D3 to return Yes if B3 contains GCVW-00002

I'm able to get the formula to return yes or no correctly if cell B3
contains either GCVW-00001 or GCVW-00002, but the formula won't work if it
contains both at the same time.

right now my basic formula looks like:
=IF(B3="GCVW-00001","Yes","No") or =IF(B3="GCVW-00002","Yes","No")

I'm thinking the problem is with the = portion. Is there a way to do
Conains
instead of equals?

thanks :)