Conditional Format to yellow if "?" char appears in cell

Posted by JF Kelley on September 29, 2001 10:30 AM

I've tried many variations on Conditional Formatting formula:
#VALUE! != FIND("?",$N$8)
0 != FIND("?",N8)
"" != FIND("?",$N$8)

I want cell to be yellow if there is a question mark anywhere in the cell value.

Of course, my ignorance obviously extends way beyond this, I just tried formulas:
1=1
1==1
"a" = "a"
1=FIND("A","ABC")
And I can't get ANYTHING to fire the yellow formatting except this "formula":
TRUE

What am I missing about evaluation of formulas?

Posted by Ivan F Moala on September 29, 2001 11:25 AM

Conditional format formulas are always looking
to evaluate an expression to true or false so..

=FIND("?",N8)>0

should work

You nearly had it.

Ivan

Posted by Aladin Akyurek on September 29, 2001 11:41 AM

JF,

First things first. The char "?" a reserved char like "*". It stands for any char if used with functions like search, find, etc. You need to escape it when you want the real "?".

Select the range of interest.
Activate Format|Conditional Formatting.
Choose "Formula Is" for Condition 1.
Assuming that your range start at N8, enter the following formula:

=ISNUMBER(SEARCH("~?",N8))

Note that N8 is NOT an absolute cell ref. And Note also the char ~ to escape the special meaning of "?".

Activate Format, select Yellow on the Patterns tab, and click OK.

You cannot start a formula without =-sign at the very beginning.

While

=A1=4

is syntactically correct (it evaluates to TRUE if A1 contains the value 4, otherwise to FALSE),

A1=4

is not.

All syntactically illegal. See above.

If you type TRUE in the box for "Cell Value Is", it's legal. It evalutes always to TRUE.

Hope the above helps.

Aladin

Posted by JF Kelley on September 29, 2001 12:05 PM

Thanks so much. The escape requirement for the special character and the ISNUMBER test on "#VALUE!" moved me way ahead on this.

The "=" preface was just dumb on my part; I thought the Conditional Formatting GUI was adding that for me. It was, but only after re-writing my test with defeating quotes!

Posted by peter on September 29, 2001 1:40 PM

Ivan's formula =FIND("?",N8)>0 works for me (without the tilde)

Posted by Aladin Akyurek on September 29, 2001 2:13 PM

Yep. It does...

within the conditional formatting context.

I usually set up a formula outside that context and then bring the formula in. Tested with SEARCH, not with FIND. Whence tilde.

Lets take the following sample in A1:A5.

a?
12?4
ans
1245
?

[1] In B1 enter: =FIND("?",A1) [ copy down ]

[2] In C1 enter: =SEARCH("?",A1) [ copy down ]

Compare [1] and [2]. This is funny. It seems the case-sensitive FIND ignores the special meaning of "?". I DIDN'T KNOW THIS. Ivan: DID YOU?

[3] In D1 enter: =ISNUMBER(FIND("?",A1)) [ copy down ]

[4] In E1 enter: =ISNUMBER(SEARCH("?",A1)) [ copy down ]

Compare [3] and [4].

[5] In F1 enter: =ISNUMBER(SEARCH("~?",A1))

The latter is what I immediately had in mind. I seldom use case-senstive FIND and it didn't occur to me that FIND and SEARCH would behave differently, a difference that doesn't seem to be motivated.

Thanks for testing Ivan's formula. It was worth it.

Aladin