0

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.

2
  • Welcome to Web Applications Stack Exchange. Nov 23, 2022 at 20:51
  • You might tell us why you are doing this as you could get the exact same answer simply by =FIND("@",I2) since the position of @ and @gmail (or @yahoo, or @hotmail, etc.) are the same. If you are just trying to split the user portion of the address from the domain portion you don't need to search for the domain names.
    – Blindspots
    Nov 23, 2022 at 21:15

2 Answers 2

0

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.

1
  • Thank you very much! Nov 24, 2022 at 2:17
0

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)

Your Answer

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

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