Search String For Character & Return Zero If Not Found

Kadence · Jan 16th 2008

How can one do a string search in Excel that searches for a string, and rather than returning a #VALUE! error if the string is not found, instead returns 0?

For instance, =SEARCH("z","word") returns #VALUE! Is there a function that would instead return 0 or FALSE?

Kieran · Jan 16th 2008

Re: String Search Function That Doesn't Return #value Error?

Kadence,

Try the following formula.

=IF(ISERROR(SEARCH("z","word")),0,SEARCH("z","word") )

Dave Hawley · Jan 16th 2008

Re: String Search Function That Doesn't Return #value Error?

=IF(ISNUMBER(SEARCH("z","word")),SEARCH("z","word"),0)

**Domenic** · Jan 16th 2008

Re: String Search Function That Doesn't Return #value Error?

If you're only interested in whether the text string exists and not its position, try...

=ISNUMBER(SEARCH("z","word"))+0

...which returns 1 if found and 0 if not found.

Hope this helps!

Kadence · Jan 16th 2008

Re: Search String For Character & Return Zero If Not Found

Thanks for the quick respones I was unaware of the iserror() and isnumber() functions, very useful.

Search String For Character & Return Zero If Not Found

Participate now!

Share