Conditional Formatting Based on Question Mark (*NOT* used aswildcard)

[google]

I'm setting up a mostly text matrix (used for tracking tasks and
priorities in a larger project). What I'd like to do is set up
Conditional Formatting to highlight any cell which contains a question
mark, so that it will be blatantly obvious any time anyone on the team
enters a question anywhere in the matrix (whether as "notes" or in a
specific field to show that they don't know what to put there).

The problem is that in Excel 2007's implementation of Conditional
Formatting, the question mark appears to *only* be usable as a
wildcard which represents any character, and I cannot find any way to
tell Excel that I want to test cell contents for the actual question
mark character. I've tried "?" and '?' which only results in matching
for the quotation mark(s) with any character between them.

Does anyone know how to accomplish this? Is it even possible in Excel
2007?

Thanks!

 

[driller]

its a nice question..but i cant directly give you a formula since i dont have
my xlsoffice in this comp...
I guess others may reply you with some *if*+*find*+*char* blend of functions
for your conditional formulation that may highlight most of your *Question*
concerns in the matrix.

 

[T. Valko]

I don't have Excel 2007 but it should have an option to let you define
custom rules using a formula. In previous versions there was a "Formula Is"
option. This will work using a "Formula Is" type of option:

=COUNTIF(A1,"*~?*")

 

[google]

Unfortunately it seems that any knowledge from versions previous to
2007 isn't applicable to the new Conditional Formatting paradigm in
2007. It's *entirely* different, using a rules-based system (similar
to Outlook Inbox rules wizards) and including a lot of new formatting
options that didn't even exist before.

While there are a lot more options to choose from, it seems to be very
prescriptive in the sense of not providing the ability to create free-
form formulas. There may be lots of options, but what you want had
better be within that predefined list. I'd be very happy if someone
proved me wrong on this and could tell me a way to accomplish what I
want.


Guy

 

[T. Valko]

I've tried "?" and '?'

The key to getting Excel to recognize ? as a character and not a wildcard is
by preceding it with the tilde character: ~?. The same is true with the *
wildcard vs the * character: ~*.

So, whatever you did in trying "?" and '?', try it like this ~?

 

[florence.cabon]

The key to getting Excel to recognize ? as a character and not a wildcard is
by preceding it with the tilde character: ~?. The same is true with the *
wildcard vs the * character: ~*.

So, whatever you did in trying "?" and '?', try it like this ~?

I confirm that in 2007 as in the previous versions, adding a tilde
before the ? converts ? from a wild card to a normal character.
In the cf, chose "new rule" "apply cf to cells containing" then select
on the left menu "specific text" (I guess this should be the label but
my excel version is french, this is the second item in the list). Then
type ~?
It works (tested)
Misange

 

[T. Valko]

I got some help on this from MVP Niek Otten:

*********

On the Home tab, choose Conditional Formatting>Use a formula to determine
which cells to format>Format values where this formula
is true, fill in the formula, Format>Fill, choose a color

The formula might be something like

=NOT(ISERROR(FIND("?",A1)))

Do you answer the post?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

**********

--
Biff
Microsoft Excel MVP

I've tried "?" and '?'

The key to getting Excel to recognize ? as a character and not a wildcard
is by preceding it with the tilde character: ~?. The same is true with the
* wildcard vs the * character: ~*.

So, whatever you did in trying "?" and '?', try it like this ~?

 

[google]

Thank you all for your responses! It turns out that the tilde
character was the trick I needed, and I didn't have to set up any
special rules. I just used the cascading menus from CF within the Home
tab, selected "text contains" and put "~?" into the resulting field.

Hooray! Mission accomplished!

Guy

 

[T. Valko]

Good deal. Thanks for the feedback!