Search code examples
regexfiltergoogle-sheetscontainsgoogle-sheets-formula

How to select multiple values from a filter in Google Sheets?

I've got lots of data in a Google sheet (I do not have Excel or Windows as I am on a Chromebook) and I want to use one column to filter out cells which contain two different words. The column of data might contain various values.

Example

Cell 1 Acme - Main - Location
Cell 2 Acme - Secondary - Location
Cell 3 Acme - Location - Main

Sticking with the above example, I would like to use my data filters set at the column headers to only show me cells where it matches Acme and Main.

What is the best way of doing this, please?

I tried using the Text Contains option in the data filter but I'm not sure how to insert both words as something to filter by, it seems to only filter the words exactly how they are typed. So if I type in Acme Main into the filter it will work for some cells which are in that exact order.

Solution

  • if the order of "acme main" combo does not matter you could use:

    =REGEXMATCH(A1:A, "Acme(.+)Main|Main(.+)Acme")

    0

    if you also want it by any chance case-insensitive use:

    =REGEXMATCH(LOWER(A1:A), "acme(.+)main|main(.+)acme")

    0