1

I am working in google sheets and have a manual input quantity in column E. I am trying to write a conditional format that will highlight each cell in column E (or the whole row) that is greater than 1 only if column C's matching row begins with "EM". I have a few variations but this is closest that could seem to work but cells that do not match the conditions are getting highlighted and cells with the conditions are being omitted...

=and(regexmatch(C2,"EM"),E2>1)

I know I will need some wildcards in there but I tried a few different combo's already with no luck. Thanks for any help!

0

cells that do not match the conditions are getting highlighted and cells with the conditions are being omitted

That sound like you need a NOT to negate the result.

column C's matching row begins with "EM"

The regular expression now matches all content which contains EM, so you probably need to replace the argument with "^EM"; the caret indicates the beginning of the content. (That's a standard regex operator; as indicated here, Google Sheets uses the RE2 version.)

You could try this:

=not(and(regexmatch(C2,"^EM"),E2>1))

and see whether it works for you.

  • I attempted the adding of the "not" for the scenario with and without the caret. I attempted using the caret notation earlier. And this confirms there is something that sheets does not like about it. All I was thinking I could do was to add wild cards to count the reaming characters, ^EM*****? but this is new for me. Here is a copy of what Im working on. The cells referenced are in the Check-out tabhttps://docs.google.com/spreadsheets/d/1prFAAy_NF_uigqZo7q3kuYq1JapDAC1xzpr6fHCahuY/edit?usp=sharing – Mike May 28 '20 at 12:08
  • Never mind. This seemed to work on the copied version of the doc. I will have to look into further to why its not working on my master. Thanks for the help! – Mike May 28 '20 at 12:17

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.