How do I handle error conditions with the FIND command?

[mywebaccts (at) PLUGcomcast.net]

I have a file of text strings in which some of the strings have a
substring I'm looking for, and others don't.

I tried using the FIND command to determine if the substring exists for
each text string, but when it doesn't ... I get #VALUE! returned.

I would have assumed that since FIND returns the index of the substring,
I would get something nice like "-1" returned when it's not there.

The problem is ... how do I manage the #VALUE! returns? I mean, is that
some kind of value I can test for? For instance, if I wanted to just
list the index of the substring, or write a "0" if it's not there, I
would try something like ...

=IF(FIND(substring,string,1)>0,FIND(substring,string,1),0)

But of course, it doesn't work. Can anyone help me?

Thanks!
Jack

 

[Ron Coderre]

Try something like this:

=IF(COUNTIF(string,"*"&substring&"*"),FIND(substring,string,1),0)

Does that help?
***********
Regards,
Ron

XL2003, WinXP

 

[Dave Peterson]

=if(isnumber(find(...)),"it's there","it's not there")

Just a reminder =find() is case sensitive. =Search() is not case sensitive.

 

[mywebaccts (at) PLUGcomcast.net]

Ah ... thanks! I think this will do the trick!

Try something like this:

=IF(COUNTIF(string,"*"&substring&"*"),FIND(substring,string,1),0)

Does that help?
***********
Regards,
Ron

XL2003, WinXP


:

 

[mywebaccts (at) PLUGcomcast.net]

Thanks! Yeah, I knew the case sensitive difference, but I didn't think
of using isnumber.

Thanks again.

 

[Harlan Grove]

=if(isnumber(find(...)),"it's there","it's not there")

....

Can save a few keystrokes using COUNT instead of ISNUMBER.

=IF(COUNT(FIND(..)),"found","not found")

 

[Dave Peterson]

I was wondering why you started using =count() in situations like this.

But I think that using =isnumber() is easier to understand for a single cell.