How do I filter text in a column in google sheets?

Question

I have a column that contains text and numbers in the same cell.

I want to return all the rows from a column that contain both the number 1 and the number 2.

I tried using =regexmatch(E1,"1|2") and also =match(E1, {"1","2"}, 0) but they both failed.

Please add a sample of your data. Would you want to return John1Bo2b or only 12, 21, or only 1 or 2 or John1Bob or 1JohnBob, or John 1 etc. etc. — Blindspots, Jul 12 at 18:51
Please clarify specifically what you mean by "they both failed". Did you get an unexpected result,? Did you get an error? Did it return the right result but only for one row? etc. — Blindspots, Jul 12 at 19:09
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jul 12 at 19:23

doubleunary · Accepted Answer · 2023-07-12 21:54:41Z

1

If you are using Data > Create a filter, make your filter condition refer to the first data cell, like this:

=regexmatch(E2, "\b1\b") * regexmatch(E2, "\b2\b")

To get the matching rows in another column, use a filter() formula, like this:

=filter(E2:E, regexmatch(E2, "\b1\b"), regexmatch(E2, "\b2\b"))

answered Jul 12 at 17:11

doubleunary

13.2k4 gold badges17 silver badges49 bronze badges

Don't want to write an answer as my formula would be the same however my read of the question is that the RegEx should be "1.*2|2.*1" (any cell containing both 1 and 2 anywhere in the value. The question isn't clear however "1 or 2 preceded by and followed by a word boundary" seems like a broad interpretation. Assumes the numbers are not part of strings or larger numbers. Hopefully, they clarify what they want and what "failed"
– Blindspots ♦
Jul 12 at 18:52
1

Edited the answer to require that both numbers appear in the same cell, surrounded by word boundaries.
– doubleunary
Jul 12 at 21:55
My money's on the numbers not having spaces around them but that is the fun of this right? If we're lucky the problem is so important to the user that they actually return.
– Blindspots ♦
Jul 13 at 2:34

Add a comment |

1 Answer 1