search function

  • Thread starter Thread starter roger
  • Start date Start date
R

roger

i am using a search function to evaluate a cell and return
a 0 or a 1 depending on whether the text is found.

The syntax used is as follows:

=if(SEARCH("White",J2)>0,1,0)

so i am searching a field with the word white and if it
exists it will return a value that is greater than 0 and a
1 will be placed in the cell. This part works great and
returns the desired result. If however the word White
does not exist in the cello J2, the result is #VALUE! not
0.

I tried to trap on #VALUE! to no avail.

I tried to return the value of the search, the answer is
the same.

I tried testing on Null or ISNull to no avail.

Do you have any ideas?
 
-----Original Message-----
=ISNUMBER(SEARCH("White",J2))+0

or

=--ISNUMBER(SEARCH("White",J2))




.
Thanks. I used the isnumber and then, as it returned a
Click to expand...
true or a false ran an if statement on the result to give
me a 0 or 1 so that I could sum.

Thanks for the tip.

Roger
 
roger said:
true or a false ran an if statement on the result to give
me a 0 or 1 so that I could sum.
Click to expand...

You could have used the 2nd formula

=--ISNUMBER(SEARCH("White",J2))

whose results you can sum directly.
 
Back
Top