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

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

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

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

--
Regards
Frank Kabel
Frankfurt, Germany
"Mctabish" schrieb im Newsbeitrag
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