Google Sheets – Delete Rows Matching Multiple Criteria with Custom Formula

formulasgoogle sheetsgoogle-sheets-arrayformularegex

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?

Best 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

Related Topic