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,stri ng,1),0)

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

Thanks!
Jack

Try something like this:

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

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

XL2003, WinXP


"mywebaccts (at) PLUGcomcast.net" <"myweb" wrote:

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,stri ng,1),0)

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

Thanks!
Jack

=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" wrote:

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,stri ng,1),0)

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

Thanks!
Jack

--

Dave Peterson

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

Ron Coderre wrote:
Try something like this:

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

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

XL2003, WinXP


"mywebaccts (at) PLUGcomcast.net" <"myweb" wrote:


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,st ring,1),0)

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

Thanks!
Jack

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

Thanks again.


Dave Peterson wrote:

=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" wrote:

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,st ring,1),0)

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

Thanks!
Jack

"Dave Peterson" wrote...
=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")

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.


Harlan Grove wrote:

"Dave Peterson" wrote...
=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