matching problem
 

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.

matching problem
 

Something simple like:

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Seeker" wrote:

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.

matching problem
 

Tks JBeaucaire

"JBeaucaire" wrote:

Something simple like:

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

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Seeker" wrote:

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.

matching problem
 

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",A 1))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Seeker" wrote:

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.

matching problem
 

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

Try this:

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

--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in message
...
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",A 1))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Seeker" wrote:

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.