Formula help wildcard text

[MeaclH]

G'day,

I need a formula to do the following.
If cell contains a value with the number "0" anywhere in the referenced cell, the number "1" is returned, and if it contains a value with the text "EDO" anywhere in the cell a ".5" is returned. If neither of these is found a 0 is returned.

Any help would be greatly appreciated

Thanks
Hayden

 

[hagia_sofia]

Hello, and what if a cell contains both 0 and "EDO" - what whould be returned then?

 

[MeaclH]

Hello, and what if a cell contains both 0 and "EDO" - what whould be returned then?

Hi there,
It would be unlikely/impossible in our current sheet set up for this to occur.

Actually while I think further, there may be some additional scenarios that would need to be counted.

Could you help with a formula for any of the following please?

If cell contains any of the following, 1 is returned

*0*
SDO
CDO
PHC

If cell contains “EDO”, .5 is returned.

For anything else, 0 is returned.

Thanks heaps

 

[Phuoc]

Try this:

=LOOKUP(1,-SEARCH({"*","EDO","SDO","CDO","PHC","0"},A2),{0,0.5,1,1,1,1})

 

[MeaclH]

Try this:

=LOOKUP(1,-SEARCH({"*","EDO","SDO","CDO","PHC","0"},A2),{0,0.5,1,1,1,1})

This is epic!
Thanks heaps