Excel formula find text, if true, display a number?

Question

Hi, new to excel 2007. I wrote this =IF(SEARCH("Organic", A5),"1", "0") to search A5 cell to see if it contains the word organic in entire cell, if it does, show 1, if not, show 0. I keep getting #VALUE! error and it seems to be the A5 cell being the problem. Both search from and display cell are in the same general or text formats (tried both). Nothing works! Help this newbie please!

Answer 1

Dude you should call Microsoft because I doubt anyone is going to be able to help you using the information provided.

Answer 2

Try this one:

=IF(ISERROR(SEARCH("Organic",A5)),0,1)

Now, that one will state 0 if it isn't there, 1 if it is. Here's how:

First, we have the classic IF() function. Now, the ISERROR is there to detect whether the SEARCH function throws an error. It will always throw a VALUE error if the text is not found. So, we have to test it that way.

The 0 and 1 have to be reversed, since we are really asking it the opposite question (instead of "Does this appear in that cell?", we are asking "Does this not appear in the cell")

This will return the value if "Organic" appears anywhere in the cell. So, if the cell has "I am an organic cheese ball" in it, it will return 1 just as it would if you simply entered "Organic". It is not case sensitive.

Problem solved, yo.

Answer 3

I guess your trying to find if the word organic exists within a cell that contains a sentence or a str rite?

you were just missing a error trapping function in your function.  also notice, the search function returns #VALUE! if not found or the position of the text you want to find in that cell.  so inorder to achieve what you want with displaying 1 and 0 instead of wat the search function returns, use the function below.

=IF(ISERROR(SEARCH("organic"

,A5)),"0","1")

i dont know y yahoo cuts off the function with ... so i have to display it in 2 lines.

Basically, the #VALUE! your getting is because the word organic is not found in the cell A5.  so inorder to trap this error, you use the ISERROR() function to check if an error was returned.  if an error was return, display 0 (not show) or if no error, return 1 (show).

Answer 4

Search function does not return a logical value. For IF, you NEED a function that returns True or False.

Use this:

=IF(EXACT("Organic", A5),"1", "0")