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
6

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

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.