Partial Match - domains to email addresses

%3CLINGO-SUB%20id%3D%22lingo-sub-2874902%22%20slang%3D%22en-US%22%3EPartial%20Match%20-%20domains%20to%20email%20addresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2874902%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20columns%2C%20column%20A%20with%20domain%20names%20(around%20500)%2C%20column%20B%20with%20email%20addresses%20(around%205000)%20-%20how%20can%20I%20identify%20email%20addresses%20that%20contain%20the%20domains%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20my%20list%20of%20domains%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3Eyahoo.com%3C%2FP%3E%3CP%3Enike.com%3C%2FP%3E%3CP%3Eadidas.com%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20identify%20email%20addresses%20that%20contain%3A%26nbsp%3B%40yahoo.com%2C%26nbsp%3B%40nike.com%20%2F%20%40adidas.com%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2874902%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2875015%22%20slang%3D%22en-US%22%3ERe%3A%20Partial%20Match%20-%20domains%20to%20email%20addresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2875015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1193364%22%20target%3D%22_blank%22%3E%40supermajda%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20conditional%20formatting%20to%20identify%20all%20email%20addresses%20for%20a%20certain%20domain.%20I%20entered%20data%20validation%20for%20all%20domains%20(3%20in%20this%20example)%20in%20A1%20and%20chose%20%22%3CSPAN%3Eadidas.com%22%20and%20all%20email%20addresses%20containing%20%22adidas.com%22%20in%20column%20B%20are%20highlighted.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20entered%20this%20formula%20(rule)%20for%20conditional%20formatting%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DCOUNT(FIND(%24A%241%2CB1))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eand%20in%20the%20manager%20for%20conditional%20formatting%2C%20in%20the%20field%20%22applies%20to%22%20i%20entered%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D%24B%241%3A%24B%241000%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20can%20be%20adjusted%20to%20your%20requirements.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have two columns, column A with domain names (around 500), column B with email addresses (around 5000) - how can I identify email addresses that contain the domains? 

 

For example, if my list of domains is: 

yahoo.com

nike.com

adidas.com

 

I need to identify email addresses that contain: @yahoo.com, @nike.com / @adidas.com 

 

Thank you! 

 

2 Replies

@supermajda 

I suggest conditional formatting to identify all email addresses for a certain domain. I entered data validation for all domains (3 in this example) in A1 and chose "adidas.com" and all email addresses containing "@adidas.com" in column B are highlighted.

I entered this formula (rule) for conditional formatting;

=COUNT(FIND("@"&$A$1,B1))

and in the manager for conditional formatting, in the field "applies to" i entered:

=$B$1:$B$1000

This can be adjusted to the requirements.

Thank you so much for your time and advice! @Quadruple_Pawn

I am not super familiar with data validation either, would you mind attaching your spreadsheet?
I am testing out your formulas as we speak.

Thanks again!