1

In Sheet 1 I have a list of emails and I would like to add a status (either Good or Bad / true or false) next to it if the email contains any of the substrings in the Sheet 2.

Sheet 2 has a list of substrings that constitute a "bad email" such as @test.com or noreply that are in a list of cells.

Here is the desired output

I've searched around and cannot find similar questions so I think this would be helpful for those searching for the same problem in the future.

1

Use textjoin() to build a regular expression and use it to test the email addresses, like this:

=arrayformula( 
  ifs( 
    isblank(A2:A), iferror(1/0), 
    regexmatch( A2:A, textjoin("|", true, Bad!B2:B) ), "Bad", 
    true, "Good" 
  ) 
)

To learn the exact regular expression syntax used by Google Sheets, see RE2.

1
  • Thanks a ton! Really. Never would have been able to figure this out on my own
    – Ian Thomas
    Jun 3 at 4:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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