How to limit the email domains in an Excel cell

Question

I have a column in excel with 150 rows with different Email Addresses for different companies. There is one row per company. Each cell contains multiple (up to 26) email addresses in the following format:

• [email protected];[email protected];[email protected];[email protected]
• [email protected];[email protected]
• [email protected];[email protected]
• [email protected];[email protected]

I want to limit the email domains that can be used in each row and have a column to indicate if an invalid email domain in being used. In the example above, the following domains should be accepted for each row:

• @google.com, @abc.com
• @twitter.com
• @facebook.com
• @instagram.com

This means that I should have a column with the following status for each row:

• Fail
• Pass
• Pass
• Fail

Is there a way I can do this with an Excel formula?

Thank you!

I have an idea:

1. Take the email domains
2. Remove duplicated ones as there are multiple addresses with the same domain
3. Create a database of accepted domains per company
4. Compare how many different domains are being used for each row and how many are accepted for the respective company.

The problem is that I am lost with the first two steps as I don't know how to extract the domains for multiple emails in a single cell.

Answer 1

Here is an option:

Formula in C1:

=MAP(A1:A4,B1:B4,LAMBDA(a,b,@SORT(IF(ISNA(XMATCH("@"&DROP(TEXTSPLIT(a,"@",";"),,1),TEXTSPLIT(b,", "))),"Fail","Pass"))))