matching problem

S

Seeker

Could anyone to help in this ?
I have a cell containing combination of text which could have three
different set of data, 1) "YY 10.1", 2) "OCO 20.1", 3) "YY 10.1 OCO 20.1"
where all figures are not constant, it could increase or decrease to 1.12 or
100.1.

I would like to set an IF formula by matching the "YY" and "OCO" in this
cell but no need to match the figures, say

If cell contains YY than "1", If cell contains OCO than "2", If cell
contains YY and OCO at the same time than "3".

I can identify 1) & 2) by using =RIGHT() but don't know how to identify
condition 3), is there any built in formula that I can use to get result 3)?
Tks in advance.
 
J

JBeaucaire

Something simple like:

=IF(ISNUMBER(SEARCH("*YY*",A1)),1,0)+
IF(ISNUMBER(SEARCH("*OCO*",A1)),2,0)
 
S

Shane Devenshire

Hi,

Here is another alternative if YY and OCO are always capitals:

=ISNUMBER(FIND("YY",A1))+ISNUMBER(FIND("OCO",A1))

If they may be either case:

=ISNUMBER(SEARCH("YY",A1))+ISNUMBER(SEARCH("OCO",A1))
 
T

T. Valko

I think you missed this part of the explanation:
If cell contains YY than "1", If cell contains OCO than "2",
If cell contains YY and OCO at the same time than "3".
Click to expand...

Try this:

=SUM(COUNTIF(A1,{"*YY*","*OCO*"})*{1,2})
 
Top