Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have two columns of data. Column 1 contains a list of email address domains such as gmail.com, yahoo.com, mydomain.edu, myotherdomain.org etc. The second column contains a list of complete email addresses, ex. myemailaddress@domain.com.

I want to highlight every email address in column B that has a substring match of one of the domains in column A.

For example, in column B I have the address test123@domain1.com and the string domain1.com in column A. That address in column B should be highlighted. If I have anothertest@domain2.com in column B but the string domain2 does not appear in column A, the email address must not be highlighted.

How can I do this?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Use conditional formating and insert this formula to determina which cells to format

=IFNA(MATCH(MID($B1,SEARCH("@",$B1)+1,999),A:A,0)>0,FALSE)

You can double check the formula by placing it in an emtpy column and check its result

enter image description here

Note: On Excel versions prior 2013 =IFNA() must be replaced by =IF(ISNA())

=IF(ISNA(MATCH(MID($B1,SEARCH("@",$B1)+1,999),A:A,0)),FALSE,TRUE)

Syntax explained from inner to outer parts

  • SEARCH("@",$B1) will determine the position of the @-sign for each cell of column B.
    The result for the example above will be 8 and 5.

  • MID($B1,[...]+1,999) will cut out everything right from the @ char. Notice that we have to add +1 to the start position or else the @ sign will be included. The result in our example would be domain1.com and domain2.com.

    The length parameter can't be omitted so I chose 999 to be safe.

  • MATCH([...],A:A,0) will search the domain string in column A and give back the row index. (Dammit, we need false or true). Example results would be 2 and #NA since the second email has no matching domain in column A.

  • ISNA([...])) is used as a small trick to convert row indices to true or false. If we have a valid match, a row index is delivered and thats not #NA so we get false. But if the inner formula result in #NA the ISNA formula will give us true.

    Wait, but that is switched! Yes, we need it the other way around.

  • =IF([...],FALSE,TRUE) just switches true and false so we can use it as input for the conditional formula

share|improve this answer
    
You can use IFERROR() instead of IFNA() on older excel versions :) –  Jerry Aug 15 '13 at 19:41
    
@Jerry Hey you, can you please check my formulas? I only have a non-english Excel and sometimes I mix up the english formula names. And thanks for the IFERROR hint, will include it after your response. :) –  nixda Aug 15 '13 at 20:11
    
Haha, yup, they're fine n_n –  Jerry Aug 15 '13 at 20:14
    
Uh, unfortunately =IFERROR isn't available in Office 2003. Lets stick with the second formula version which seems to be most backward compatible –  nixda Aug 15 '13 at 20:24
    
Ah, I don't have 2003, but only 2007 ^^; –  Jerry Aug 15 '13 at 20:26
add comment

A more compact formula for the conditional formatting rule:

=COUNTIF(A:A,MID(B1,FIND("@",B1)+1,999))

This formula works as follows, assuming the first email is in cell B1:


  FIND("@",B1)+1           :  finds the first character of the domain name in 
                              an email address (which begins one character 
                              beyond the @)

  MID(B1,FIND(...)+1,999)  :  extracts the full domain name from the email 
                              address

  COUNTIF(A:A, MID(...))   :  counts up the number of matches in column A of
                              the domain name in the email address (which  will 
                              be at most 1 if the domain list contains no 
                              duplicates).

To use the formula, you would select the range to be formatted, open the conditional formatting dialog, choose to create a new formatting rule based on a formula, paste the formula in, and then set the format you want to highlight the matching cells.

The Excel conditional formatting engine will treat as TRUE the 1 produced by the formula for a matched domain name, and as FALSE the 0 for a non-match. (Even if there are duplicates in the domain list, and thus counts greater than 1, the formula will still work.)

This formula is compatible with Excel 2007 and later versions. The following amended formula should be backward-compatible with all prior-year versions of Excel. It assumes the domain list is in the range A1:A10.

=SUM(IF($A$1:$A$10=MID(B1,FIND("@",B1)+1,999),1,0))>0
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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