Function search returns #VALUE!
Solved
gregmichel
-
gregmichel -
gregmichel -
Bonjour,
I am facing an issue with the search() function:
In one column, each cell is filled with a string: name of a person, company name, or name of a university/research center.
I am trying to automatically annotate universities and research centers in a new column. I wrote the following function which returns #VALUE!
=IF(OR(search("university",cellA1,1),search("center",cellA1,1)),"Yes","No")
I also tried to nest the IF() functions but it still doesn't work: when the SEARCH() function doesn't find "university", it returns #VALUE! and nothing works anymore, so I end up with #VALUE! in the cell even if I have "center".
Example table:
Column A Column B Column C (desired result)
Doc 1 Company A No
Doc 2 University B Yes
Doc 3 Paul and Pierre No
Doc 4 Center C Yes
Doc 5 University D Yes
Thank you for your help
Gregoire
I am facing an issue with the search() function:
In one column, each cell is filled with a string: name of a person, company name, or name of a university/research center.
I am trying to automatically annotate universities and research centers in a new column. I wrote the following function which returns #VALUE!
=IF(OR(search("university",cellA1,1),search("center",cellA1,1)),"Yes","No")
I also tried to nest the IF() functions but it still doesn't work: when the SEARCH() function doesn't find "university", it returns #VALUE! and nothing works anymore, so I end up with #VALUE! in the cell even if I have "center".
Example table:
Column A Column B Column C (desired result)
Doc 1 Company A No
Doc 2 University B Yes
Doc 3 Paul and Pierre No
Doc 4 Center C Yes
Doc 5 University D Yes
Thank you for your help
Gregoire
1 answer
-
Hello
This should work better:
=IF(ISERROR(SEARCH("University";B1;1));IF(ISERROR(SEARCH("center";B1;1));"No";"Yes");"Yes")
Pay attention to spelling and accents.
Always zen
Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away. Antoine de Saint-Exupéry
