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?


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

  • Please hover over the tags in your question and read the summary. – DavidPostill Aug 19 '15 at 11:47
  • @DavidPostill Thanks, man, I did it so quick that I forgot the summary... But I think tags are ok... – kokbira Aug 20 '15 at 11:01
  • @kokbira: I think DavidPostill's point was that the tag summary for Google Spreadsheet at Super User tells you that questions about Google Spreadsheets are off-topic at Super User. Fortunately, your question has been migrated to the right place. – ale Aug 20 '15 at 19:04

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

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.