Unwanted #VALUE! when searching for text using either SEARCH or FIND

[charlesstricklin]

Maybe it's just morning and my brain isn't working yet, but I'm struggling with the SEARCH function. I want to search a cell for a text snippet, and if it's found return "Yes"and if not, simply a null string/leave a blank. I.e.:

C2 being equal to "Foobar"

=IF(SEARCH("Foo",C2),"Yes",""))

However, when the text is not found, it returns #VALUE! instead.

I'm certain I'm not doing something extremely simple, but I can't seem to wrap my brain around it this morning. Please help?

 

[SQUIDD]

would this work for you?

=IFERROR(IF(SEARCH("foo",C2,0),"yes",""),"")

dave

 

[etaf]

you can use iferror or iserror

=IF(iserror(SEARCH("Foo",C2)),"","Yes"))

 

[SQUIDD]

or try this

=IF(ISNUMBER(SEARCH("foo",C2)),"yes", "")

dave

 

[nithin shettigar]

	B	C
2	Foobar	YES

<tbody>
</tbody>

=IFERROR(IF(SEARCH("FOO"&"*",B2),"YES",""),"")

This may help you

 

[charlesstricklin]

or try this

=IF(ISNUMBER(SEARCH("foo",C2)),"yes", "")

dave​

This was the winning formula, thanks!

 

[SQUIDD]

your welcome

dave