We usually don't have to conceptualize it this way, but in the "conditions" part of FILTER
, we're actually setting up an array of TRUE
or FALSE
results to control whether each input row makes it through the filter. Perhaps take advantage of that and negate the Booleans outside the REGEXMATCH
?
=FILTER(Planning!G$5:G$37; NOT(REGEXMATCH(Planning!G$5:G$37; "a")))
That REGEXMATCH
silently errors out on-text values, so I'd also convert to TEXT
before the check.
=FILTER(Planning!G$5:G$37; NOT(REGEXMATCH(TEXT(Planning!G$5:G$37;); "a")))
Lastly a side note, formulas that work on arrays don't work with the native boolean OR
and AND
functions, but we can instead use +
and *
respectively. This formula requires FILTER
matches to NOT
pass the REGEXMATCH
, AND
to have non-zero LEN
(length):
=FILTER(Planning!G$5:G$37; NOT(REGEXMATCH(TEXT(Planning!G$5:G$37;); "a")) * LEN(Planning!G$5:G$37))
…if that better suits your actual spreadsheet and formulas rather than just having a second FILTER
condition:
=FILTER(Planning!G$5:G$37; NOT(REGEXMATCH(TEXT(Planning!G$5:G$37;); "a")), LEN(Planning!G$5:G$37))