How to get a true when a character is NOT in range

Question

I want to filter all cells in a range which are not empty and does not contain a certain character. So I tried everything with regexmatch

=filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "![a]"))

---

=filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "<>a"))

---

=filter(Planning!G$5:G$37; regexmatch(Planning!G$5:G$37 ; "\a"))

but not even my simplified formula where I cut out the " ! not empty" part, doesn't give me any result.

My question: So what is the proper way to show the cells that don't contain the letter 'a'

Answer 1

=QUERY(A1:A10, "where not A contains 'a' and A is not null")

Answer 2

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