Google Sheets Highlighting using Conditional Formatting for Text that DOESN'T Contain “x” - TWO Criteria

0
votes

I plan to use Google Sheet's conditional formatting to highlight cells where the text DOES NOT contain:

  • Retail
  • FinServ
  • Manufacturing
  • Field Service
  • Managed Services
  • Digital Transformation
  • Ecommerce
  • Data and Analytics

For the above phrases, I want to be able to add additional details, separated by an underscore (_), and have the row still NOT be highlighted. For ex: Retail_Blog should still NOT be highlighted because it begins with one of the phrases above.

To do this, I'm currently using the formula:

=regexmatch(F:F,"Retail|FinServ|Manufacturing|Field Service|Managed Services|Digital Transformation|Ecommerce|Data and Analytics")=FALSE

This formula works great for the specifications above, but I also would like the formula to do adhere to another rule.

For the phrases below, I would like the formula to highlight cells if they DON'T EXACTLY match the phrases. For ex: "Meetings" should NOT be highlighted, but "meetings," "Meeting," and "Meetings_whatever" SHOULD be highlighted.

  • Meetings
  • Website Updates
  • Press Release and Distribution
  • Calendar Planning

Also, this formula would be for the range F:F.

1
regexgoogle-sheetsgs-conditional-formatting
The formula uses case sensitive regular expressions. Have you already tried to do something similar like adding the second set of phrases separating them by |? Have you considered to use Google Sheets functions like AND(), OR(), SEARCH(), FIND() among other? – Rubén
By the way "Let me know..." invites primarily opinion based answers which could make the question to be closed. – Rubén
Combine the second set with NOT(regexmatch(,"|")) – TheMaster
@Rubén the function is currently working by separating the terms with | and it didn't with commas, so that wouldn't work in this case. SEARCH and FIND are only looking for the first occurence, and there may be many occurrences I need to track. The OR function wasn't working for me which is why I went with regexmatch. – Katie Rosenholm
@I'-'I I tried that and it's just highlighting whatever isn't being highlighted from the first function and not following my specific rules – Katie Rosenholm

1 Answers

0
votes

Formula

=regexmatch(F:F,"Retail|FinServ|Manufacturing|Field Service|Managed Services|Digital Transformation|Ecommerce|Data and Analytics|^Meetings$|^Website Updates$|^Press Release and Distribution$|^Calendar Planning$")=FALSE

Explanation

  • ^ means match start
  • $ means match end