A couple of questions regarding SEARCH

Hello, I have thousands of rows in column A, and each row is a string. I need to choose only those rows that do contain a ":" but do not contain one or more "?" in their respective strings. For example, the first row contains the string "Davis D.R. 1991. see Rami Davis R. 1991." It does not contain "?" but neither does it contain ":". So I would not be choosing it. I tried the following:

I created a new column (B) with the formula: =IF(SEARCH(":",A2),"KEEP","NOKEEP"), but it returned "#VALUE!". Not sure why. Based on a post in this forum, I then changed my formula to =IF(ISNUMBER(SEARCH(":",A2)),"KEEP","NOKEEP"), and that worked (it returned "NOKEEP" correctly, because the string does not contain ":").

I then created a second column (C) with the formula =IF(ISNUMBER(SEARCH("~?",A2)),"NOKEEP","OTHER") to determine if the string contains "?". And that worked as well.

And finally a third column (D) with the formula =AND(B2="KEEP", C2="OTHER"), and it returned TRUE or FALSE for each row (for example, it correctly returned FALSE for the first row). I then sorted all the rows to get only those that returned TRUE in this column.

My questions are:

1. I don't see the relevance of "ISNUMBER" in the formula in column D. The formula is searching a string in Column A.
2. There must be an easier way of accomplishing the above in one formula. Is there?

Thank you, Dave. That was indeed my first question to the forum. Thanks also for the quick reply. Appreciate it. So if I understand your answer, since IF expects a TRUE/FALSE reply but SEARCH only returns
a number or an error (#VALUE!), one needs to add ISNUMBER so that the result is TRUE or FALSE. In this context, of course the result will always be FALSE because the column only contains strings. Will it
make a difference if the result happened to be TRUE? Also, if that is the reason for using ISNUMBER, perhaps one can use any function that results in a TRUE/FALSE answer here?

Also, I wonder if you have any response to my second question - is there a shorter (smarter?) way of achieving what I need rather than the three-step process I used?

Thanks,

Serge

Try this:

=AND(ISNUMBER(SEARCH(":",A2)),ISERROR(SEARCH("~?",A2)))

or:

=AND(COUNTIF(A2,"*:*"),NOT(COUNTIF(A2,"*~?*")))

Thank you, Dave, for the point-by-point answers. Really appreciate your help.

And thank you, Phuocam. Both your solutions worked! They seem very clever, but I still need to parse them to understand exactly what they are doing. But that's terrific! Thanks again.

Now, I'd like to acknowledge both of you by giving you reputation points. I need to figure out how to do that.

Thanks again.

Just marked it as Solved. Thanks!