Filter multiple values using filter by condition tool in Google Spreadsheet

Question

I'm trying to filter a long list of URLs to narrow down which ones I'm interested in. I've got my URL list in Google Spreadsheet and I'm using the filter tool to remove data that I don't want.

At the moment using the filter setting: Filter by condition, text does not contain.

This works fine but so far I've only been able to set input 1 value in there. I.e. pinterest

What I want to do is put multiple values into that box IE. "pinterest" OR "facebook" OR "twitter" or something like pinterest, facebook, twitter. Like the image below (although if I run this it doesn't filter at all.. any ideas?

I've created a shared spreadsheet here to test in

Answer 1

You can filter using a regular expression. Use the options Filter by condition > Custom formula is...

=not(regexmatch(N:N, "(?i)facebook|pinterest|twitter"))

to see only the strings that do not contain one of the above.

Here N is the column you're filtering, and the regular expression means: the string contains match one of words facebook, pinterest, twitter, case-insensitive. And not is the negation of that.

Answer 2

And for those who are using the "custom formula" option you will need to know that the formula is based off the cell immediately under the filter. So if your have a filter in Cell A1, use A2 in the custom formula.

Here is an example

=(A2=$C$1)

Answer 3

The REGEXMATCH() certainly works but it's hard to remember and, according to my limited testing, much slower than simply using an OR() like this:

=NOT(OR(N2="facebook",N2="pinterest",N2="twitter"))

On a 250,000 cell sheet, using a filter with 135 strings (which is simply the actual data I wanted to filter), the REGEXMATCH() version takes 22 seconds to filter the sheet whereas the OR() version takes about 3.5 seconds.

The REGEXMATCH() certainly has the potential to make much more complex and powerful searches so it's well worth knowing.

Note: In my example I'm assuming you have a header in the first row, so the reference to the data in column N is set to look at the second row (where the actual data to be filtered starts); N2 (thanks to John Pratt's answer above for that!). If you have no header row simply use N1 instead.

---

Along those same lines; this version of the REGEXMATCH() from user79865's answer is more efficient, taking about 20 seconds (about 10% faster) on my sheet, because it doesn't concatenate the whole column and apply the REGEXMATCH() to the result of that, for every row.

=REGEXMATCH(N2,"(?i)facebook|pinterest|twitter")

The only difference to user79865's answer is that this version uses N2 instead of N:N (or N1 if you have no header row).

Answer 4

I guess you can do it as Filter by condition --choose last option "Custom formula is " and in the text box put the formula as =regexmatch(TO_TEXT(N:N"),"facebook|pinterest|twitter")

In my case i have huge spreadsheet with more than 1000 rows in which i need to filter unique ids. ( more than 200) . So I used as =regexmatch(TO_TEXT(O:O),"1504761|........|1565231")