1

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'

1

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

enter image description here

1

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

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.