Search code examples
regexgoogle-sheetsgs-conditional-formatting

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

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.

Solution

  • 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