4

I have a sheet with following values on column A:

    [         A           ]
[1] Dead line
[2] 15 days remaining***
[3] Dead
[4] 131 days remaining*
[5] 80 days remaining**

I would like to use conditional formatting on column A so:

  • when only 1 asterisk appears: green cell background
  • when only 2 asterisks appear: yellow cell background
  • when only 3 asterisks appear: orange cell background
  • when column is Dead: red cell background

But when I set to A:A the rule text contains with value * to paint with green background, the whole column is painted with green background, regardless of other rules.

I see that the * is interpreted as an wildcard to "any string", but I would like to threat each * as one asterisk character only.

Someone can help me?

PS:

  • The final sheet must use this format of data on column A, with those awful asterisks (no way to change);
  • A1 is a header;
  • It is only a sample. Original sheet has so many lines...

migrated from superuser.com Aug 20 '15 at 18:56

This question came from our site for computer enthusiasts and power users.

5

You can achieve your desired results by putting a tilde, ~ in front of the asterisk as an escape character, if you put the conditional formatting rules in the order listed below.

First, create the one for orange when three asterisks occur using text contains and then specifying ~*~*~*. Select custom to pick an orange background. Then create the one for two asterisks using text contains and ~*~* picking yellow for the color. Then for the next rule create one for green with text contains and ~*. Then you can create the one with a red background using text is exactly specifying Dead and picking red for the background.

asterisk conditional formatting rules

You should then see the following:

Asterisk conditional formatting applied

  • Can't escape on assigning as the best answer :) – kokbira Aug 31 '15 at 15:46
1

An alternative and more versatile solution is to select custom formula and use regular expression matching with =REGEXMATCH(A1,"[^\*]\*{2}[^\*]") looking for asterisk sequences of the precise length 2 (to match 1 or 3 replace the number two accordingly).

  • 1
    How this could be applied to solve the specific case of the OP? – Rubén Sep 21 at 15:07
  • 1
    @Rubén: I've added a formula. Escaping the asterisk works in this particular case but is of limited versatility. OP just wanted A solution, not necessarily the one closest to what they were thinking of. – Cornelius Roemer Sep 21 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.