Need simple formula to find one string in another
 

I need a formula which returns Y if a cell contains a string and N if not.

Here's a start: IF(FIND("XXX",C3),"Y","N"). But the trouble is that if the
target string is not found, it returns a #VALUE! error.

I can of course fix that by something like
IF(ISERROR(FIND("XXX",C3),"N","Y"). But this seems unnecessarily awkward. Is
there a simpler solution?

I'm using Excel 2003.

Thanks.

Need simple formula to find one string in another
 

Try

=IF(ISNUMBER(FIND("XXX",C3)),"Y","N").

--
__________________________________
HTH

Bob

"Eric_NY" wrote in message
...
I need a formula which returns Y if a cell contains a string and N if not.

Here's a start: IF(FIND("XXX",C3),"Y","N"). But the trouble is that if the
target string is not found, it returns a #VALUE! error.

I can of course fix that by something like
IF(ISERROR(FIND("XXX",C3),"N","Y"). But this seems unnecessarily awkward.
Is
there a simpler solution?

I'm using Excel 2003.

Thanks.

Need simple formula to find one string in another
 

Is there a simpler solution?

It depends on ones definition of "simpler".

You can try this if you're not trying to match the case of "XXX".

=IF(COUNTIF(C3,"*XXX*"),"Y","N")

FIND is case sensitive. Using FIND:

xXx does not match XXX.

Using COUNTIF, case doesn't matter.

--
Biff
Microsoft Excel MVP


"Eric_NY" wrote in message
...
I need a formula which returns Y if a cell contains a string and N if not.

Here's a start: IF(FIND("XXX",C3),"Y","N"). But the trouble is that if the
target string is not found, it returns a #VALUE! error.

I can of course fix that by something like
IF(ISERROR(FIND("XXX",C3),"N","Y"). But this seems unnecessarily awkward.
Is
there a simpler solution?

I'm using Excel 2003.

Thanks.

Need simple formula to find one string in another
 

Thanks to Bob and T. Valko for your suggestions.

"Eric_NY" wrote:

I need a formula which returns Y if a cell contains a string and N if not.

Here's a start: IF(FIND("XXX",C3),"Y","N"). But the trouble is that if the
target string is not found, it returns a #VALUE! error.

I can of course fix that by something like
IF(ISERROR(FIND("XXX",C3),"N","Y"). But this seems unnecessarily awkward. Is
there a simpler solution?

I'm using Excel 2003.

Thanks.

Need simple formula to find one string in another
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Eric_NY" wrote in message
...
Thanks to Bob and T. Valko for your suggestions.

"Eric_NY" wrote:

I need a formula which returns Y if a cell contains a string and N if
not.

Here's a start: IF(FIND("XXX",C3),"Y","N"). But the trouble is that if
the
target string is not found, it returns a #VALUE! error.

I can of course fix that by something like
IF(ISERROR(FIND("XXX",C3),"N","Y"). But this seems unnecessarily awkward.
Is
there a simpler solution?

I'm using Excel 2003.

Thanks.