Search results in #VALUE! error

[Mike Welch]

I'm looking for the word "and" in a text string, A1, and want to know where it occurs. I'm using

=SEARCH("and",A1)

If it's there I get a number indicating where it's located, e.g.: 55 If it's not there I get #VALUE! - I'd rather have nothing at all. Any ideas?

Also, what if I just wanted a Yes/No response to finding the word "and"? I tried =IF(SEARCH("and",A1),"yes", "no") but that failed.

Mike Win XP Pro SP3 / Excel 2003 SP3

 

[Jonmo1]

Try

=IF(ISERROR(SEARCH("and",A1)),"",SEARCH("and",A1))


Hope that helps.

 

[T. Valko]

I'm looking for the word "and" in a text string, A1, and want to know where it occurs. I'm using

=SEARCH("and",A1)

If it's there I get a number indicating where it's located, e.g.: 55 If it's not there I get #VALUE! - I'd rather have nothing at all. Any ideas?

Also, what if I just wanted a Yes/No response to finding the word "and"? I tried =IF(SEARCH("and",A1),"yes", "no") but that failed.

Mike Win XP Pro SP3 / Excel 2003 SP3

Try one of these...

=IF(COUNT(SEARCH("and",A1)),SEARCH("and",A1),"Not Found")

=IF(COUNT(SEARCH("and",A1)),"Yes","No")

 

[Scott Huish]

=IF(ISNUMBER(SEARCH("and",A1)),"Yes","No")

 

[Scott Huish]

If you wanted to return 0 if you didn't find it, perhaps:

=LOOKUP(32768,CHOOSE({1,2},0,SEARCH("and",A1)))

 

[Mike Welch]

Thanks to all - great resolution to the problem!

 

[T. Valko]

Thanks to all - great resolution to the problem!

Good deal. Thanks for feeding back!