Google Sheets: FIND multiple cells containing specific text

Question

I have a Google Sheets table and one of the columns has email addresses.

I'm filtering that column using this custom formula that works:

=FIND("@gmail.com",I2)

But I need to be able to filter more. For example for others, email domains, like @yahoo.com, @gmail.com, @hotmail.com, etc. etc.

I'm trying to structure the formula to find multiple but no luck so far and I've been reading docs but can't figure out.

I've tried:

=FIND(I2, {"@gmail.com", "@yahoo.com", "@hotmail.com"}, 0)

=FIND("@gmail.com", "@yahoo.com", I2),

And nothing.

Any help would be greatly appreciated.

Answer 1

One way is to use a regular expression, like this:

=regexmatch(I2, "(?i)gmail|yahoo|hotmail")

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

Answer 2

I don't know exactly what do you need. A list? You may do it with a Query like this one:

=QUERY(L2:L,"Where L contains '@gmail.com' OR L contains '@Hotmail.com'"

And you can keep adding more conditionals with OR

You can do this with REGEXMATCH too, (FILTER and ARRAYFORMULA)