Help with IF/OR Formula

[Ocean Zhang]

Hello,

I need to get a formula where if A1 contains either "1" or "2" then output A1, otherwise output blank.

What I've tried isn't working. Don't laugh, but this is my best stab at it:

=IF(ISNUMBER(SEARCH("1",A1))*OR("2",A1),A1,"")

I'm getting: #VALUE!

What am I doing wrong?

[MarvinP]

Try:

Please Login or Register  to view this content.

[Ocean Zhang]

Thanks Marvin! What does ,0,1 mean?

[MarvinP]

Hi,
Let me read the intent of the formula.
If there is an error in the formula to Find a 1 in A1 then return a zero, if no error then return a one.
Do that again for finding a 2 in the number.

Now add the two numbers above together.
Could be 0 for no 1 or 2 in the number,
Could be 1 if only one is a 1 or 2,
OR the sum could be 2 if both 1 and 2 are in the number.

Now If the above sum is greater than zero, return the number, else return a blank.

Some smart guru will most like give you a much shorter formula but this one works.

[DonkeyOte]

In terms of condensing you might consider:

Please Login or Register  to view this content.

[noexcel=badwork]

another code for this:

Please Login or Register  to view this content.

[DonkeyOte]

The above is slightly different in so far as that it is an exact test rather than a partial (contains) test - the latter requirement is implied by OP.

[Ocean Zhang]

Thanks Donkey and n=b!

that's exactly what I needed. Donkey, you're right. I needed a search in rather than exact match. Just trying to educate myself: what does the count function do exactly?

[DonkeyOte]

SEARCH will either return a Number (found) or a #VALUE! error (not found)
COUNT identifies how many numbers exist in the array of values passed to it.
In this instance the array of values within the COUNT is the two SEARCH results.
It follows that if either SEARCH returns a Number the resulting COUNT will be > 0.
We can use the COUNT result to execute the appropriate part of the IF given only 0 = FALSE (all other numbers = TRUE)

[arthurbr]

Hi,

the COUNT function is well documented in XL's help which you can access with the F1 key

[Ocean Zhang]

thanks for all the help!