CODEWITHSUNDEEP
regexgoogle-sheets
Tom
Tom

Reputation: 233

Bring all values in filter except the ones in the condition

I have a list of items which I want to filter. I created a filter that includes a regexmatch with multiple criteria e.g. mo|oa (so it will bring any item that has either mo (like mouse) or oa (goat) in the string). =filter(A2:A10,regexmatch(A2:A10,C1))

What I want is to bring the remainder of the list. i.e. all the items that don't fall in the criteria I mentioned (do not include mo or oa). I have 2 questions:

  1. Is it possible to get the filter to bring the remainder of the items? If so, how?

  2. While trying to figure it out, I used <>&C1 (the cell with the condition mo|oa). =filter(A2:A10,regexmatch(A2:A10,"<>"&C1))

Surprisingly, it just disregarded the first part mo and showed only result that contain oa. I'm curious for the reason.

Here's a demo file: https://docs.google.com/spreadsheets/d/1gvTN2p2W7Agw9tIfo4ZcsBlujl0Iq0Ft8h9aYuSH4NQ/edit#gid=0

Thanks!

Upvotes: 0

Views: 77

Answers (1)

basic
basic

Reputation: 11968

You can use NOT for REGEXMATCH:

=filter(A2:A10,NOT(regexmatch(A2:A10,C1)))

enter image description here

Upvotes: 2

Related Questions