matching problem

  • Thread starter Thread starter Seeker
  • Start date Start date
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.
 
Something simple like:

=IF(ISNUMBER(SEARCH("*YY*",A1)),1,0)+
IF(ISNUMBER(SEARCH("*OCO*",A1)),2,0)
 
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))
 
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})
 
Back
Top