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 Bot
    Jul 12 at 19:23

1 Answer 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"))
  • 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. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.