7

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

screenshot

  • I can't access the spreadsheet – user79865 Jan 16 '16 at 20:59
  • Apologies, seems i didnt update the share settings, its fixed now – sam Jan 17 '16 at 0:31
12

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.

  • Thanks, @Normal, if i wanted to only show entries that do not contain facebook|pinterest|twitter how would that be written ? - I was looking at the other Regex functions Regexrepalce and Regexextract as per the google link you shared but they didnt seem suitable – sam Jan 17 '16 at 0:33
  • Use =not(regexmatch(...)) instead.. I misread the question initially. – user79865 Jan 17 '16 at 0:36
  • I just get "TRUE" or "FALSE", I don't actually get the rows that match... Am I doing something wrong? – Drewdavid Mar 16 '17 at 17:52
7

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)

  • 2
    +1K if it would be possible, one of the best advice ever! Thx very much. – danidemi Jul 18 '17 at 15:17
  • 1
    Invaluable advice! Why doesn't Google document that? – Alessandro Alinone Sep 8 '17 at 10:58
0

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).

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.