Asterisk character (no wildcard) on conditional formatting

Question

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...

Answer 1

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.

You should then see the following:

Answer 2

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).