Google Sheets中FILTER+REGEXMATCH反向筛选实现及错误用法疑问

Alright, let's tackle your two questions one by one—this is a common gotcha with Google Sheets' REGEXMATCH and FILTER combination.

1. Can we implement this reverse filtering, and how? #

Absolutely! You can easily exclude items that match your regex pattern by wrapping the REGEXMATCH function in NOT().

The correct formula for your use case is:

=FILTER(A2:A10, NOT(REGEXMATCH(A2:A10, C1)))

Here's the breakdown:

• REGEXMATCH(A2:A10, C1) returns TRUE for every cell containing "mo" or "oa" (since your C1 has the pattern mo|oa).
• The NOT() function flips those boolean values: matches become FALSE, and non-matches become TRUE.
• FILTER() then pulls all rows where the result is TRUE—exactly the items that don't fit your original matching criteria.

2. Why does =filter(A2:A10,regexmatch(A2:A10,"<>"&C1)) only show items with "oa"? #

This happens because you're mixing Google Sheets comparison operators with regular expression syntax, which don't play nicely together.

When you concatenate "<>""&C1, you end up with the regex pattern <>mo|oa. In regex logic:

• The | acts as an OR operator, so this pattern matches either the literal string <>mo OR the string oa.
• Items like "mouse" don't contain <>mo, so they don't trigger a match.
• But items like "goat" do contain oa, so they get included in the filter result.

To put it simply: <> is a valid comparison operator for basic Sheets functions (like IF() or COUNTIF()), but it has no special "not equal" meaning in regular expressions. You can't use it to reverse a regex match—you need to use NOT() as shown in the first question instead.

内容的提问来源于stack exchange，提问作者Tom