Delete Google Sheet rows matching multiple criteria with custom formula

by jsta   Last Updated July 11, 2019 19:03 PM - source

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?



Answers 1


  • 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

user0
user0
July 11, 2019 18:27 PM

Related Questions


Sum value in cells that contain both number and text

Updated February 22, 2019 10:03 AM


How to search value in another column?

Updated July 12, 2019 21:03 PM