HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB".
If there is any other content in the cell , it returns a false
conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.

Try this:

=6-COUNTIF(K1,"*AB*")

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"Colin Hayes" wrote in message
...


HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB". If
there is any other content in the cell , it returns a false conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.

=IF(COUNTIF(K1,"*AB*"),5,6)

"Colin Hayes" wrote:



HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB".
If there is any other content in the cell , it returns a false
conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.

hi
try this.

=IF(FIND("AB",A1)0,1,2)

if the formula retuns #Value!, AB not found.
adjust to suit your data.

Regards
FSt1

"Colin Hayes" wrote:



HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB".
If there is any other content in the cell , it returns a false
conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.

hi.
had a brain burp. don't use this fomula. it denys the else part
use this instead

=IF(COUNTIF(K1,"*AB*"),5,6)

sorry about that.

Regards
FSt1

"FSt1" wrote:

hi
try this.

=IF(FIND("AB",A1)0,1,2)

if the formula retuns #Value!, AB not found.
adjust to suit your data.

Regards
FSt1

"Colin Hayes" wrote:



HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB".
If there is any other content in the cell , it returns a false
conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.

=6-ISNUMBER(SEARCH("AB",K1))


"Colin Hayes" wrote:



HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB".
If there is any other content in the cell , it returns a false
conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.

In article , Lori
writes
=IF(COUNTIF(K1,"*AB*"),5,6)


Hi

OK thanks for that. Worked perfectly , first time.

Out of interest , could this formula be extended to include a second
criterion so that it would check if "AB" OR "CD" were present before
giving the true or false conclusion?


Thanks again.



"Colin Hayes" wrote:



HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB".
If there is any other content in the cell , it returns a false
conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.

=IF(OR(COUNTIF(K1,"*AB*"),COUNTIF(K1,"*CD*")),5,6)


On Feb 8, 1:45 pm, Colin Hayes wrote:
In article , Lori
writes

=IF(COUNTIF(K1,"*AB*"),5,6)

Hi

OK thanks for that. Worked perfectly , first time.

Out of interest , could this formula be extended to include a second
criterion so that it would check if "AB" OR "CD" were present before
giving the true or false conclusion?

Thanks again.



"Colin Hayes" wrote:

HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB".
If there is any other content in the cell , it returns a false
conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.

In article
, ilia
writes
=IF(OR(COUNTIF(K1,"*AB*"),COUNTIF(K1,"*CD*")),5,6 )


Hi

OK That's perfect - thanks for that.


Best wishes.



On Feb 8, 1:45 pm, Colin Hayes wrote:
In article , Lori
writes

=IF(COUNTIF(K1,"*AB*"),5,6)

Hi

OK thanks for that. Worked perfectly , first time.

Out of interest , could this formula be extended to include a second
criterion so that it would check if "AB" OR "CD" were present before
giving the true or false conclusion?

Thanks again.



"Colin Hayes" wrote:

HI

I'm using this formula :

=IF(K1="AB",5,6)

This places a number 5 in the target column if K1 contains the letters
"AB" , and a 6 if it doesn't.

The problem is that it only works for if the content is *exactly* "AB".
If there is any other content in the cell , it returns a false
conclusion.

What I really need is for this to work if the letters "AB" are contained
*anywhere* within the text in K1.

Is this possible?

Grateful for any help.