How to avoid the #Value! result in a formula?

[N Harkawat]

Use
=IF(ISERROR(SEARCH(" and ",C2)),"",LEFT(C2,SEARCH(" and ",C2)))
to avoid the error you are getting

 

[Mctabish]

I am trying to test for the word " and " in a cell. If the word is there,
everything is fine. However, if the word is not there, I get a #value!
error.
Here is the formula I have
=IF(SEARCH(" and ",C2)>0,LEFT(C2,SEARCH(" and ",C2)),"")
Column C has names and if married, the spouses name. I am trying to separate
these in to differant coulmns. The results of the above would be the
extracted spouses name

Thanks,
Mctabish

 

[Frank Kabel]

Hi
one way:
=IF(COUNTIF(C2,"* and *"),LEFT(C2,SEARCH(" and ",C2)),"")