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

"Mctabish" wrote in message
news:MXBAd.722502$mD.346465@attbi_s02...
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