Delete Google Sheet rows matching multiple criteria with custom formula

Posted on

Whether you’re just starting out with Google Sheets or are a seasoned pro, sooner or later one of your formulas will give you a formula parse error message rather than the result you want. However, every Formula user, irrespective of whether it’s a beginner or expert, has invariably come across a formula parse error in Google Sheets at least once in their life (and if you haven’t. you soon will). It can be frustrating, especially if it’s a longer formula where the formula parse error may not be obvious. In this article, we will talk about some common formula parse errors in Google Sheets, like Delete Google Sheet rows matching multiple criteria with custom formula, and how to fix it.

I have a Google sheet where I am trying to remove multiple rows matching any one of a vector of values using (Filter by condition -> Custom formula).

For this example, let’s say I want to remove the rows with id (column A) values matching either 2 or 4. This works if the values are at the top of the range:

=MATCH(A:A, 2, 4)

id      name
2       h
4       d
327432  a
358105  pp
1       f
Sunday  dd

However, if they are at the bottom of the range, I am unable to specify the search_type (=MATCH(A:A, 2, 4, 0) does not work)

id      name
1       h
Sunday  d
327432  a
358105  pp
2       f
4       dd

No combination of AND, OR, QUERY, +, *, |, or REGEXMATCH appears to do the trick. How can I do this?

Answer :

  • select Custom formula
  • type in =(A2<>2)*(A2<>4)

    0

    0


  • if you want to delete them completely, use this formula pasting it into C1 cell:

    =FILTER(A:B, REGEXMATCH(TO_TEXT(A:A), "[^2|4]"))

    and then copy range C:D and press CTRL + SHIFT + V

Leave a Reply

Your email address will not be published.