Forum Discussion

Fcin144's avatar
Fcin144
Copper Contributor
Apr 06, 2024

Identify if multiple values exist in one cell

I am trying to determine if an email address is for a client, or it is not.   So, for example our company email addresses are @nbc.com or abc.com   I would like to know if any incoming email are ...
Excel
  • Lorenzo's avatar
    Lorenzo
    Apr 07, 2024

    Fcin144 

     

    in B2:

    =IF( SUM( IFERROR( SEARCH("@", TEXTSPLIT(A2, {"@bnymellon.com","@dreyfus.com"},,,1)), 0) ), "Yes", "No")
Fcin144's avatar
Fcin144
Copper Contributor
Apr 07, 2024

V-GEe7   Lorenzo djclements 

 

Thanks for your reply. It is working however when I convert the @nbc.com and @abc.com email address conversion to my test case it's not working. I have supplied the actual test case in the screenshot below. I really appreciate everyone's help. 

 

V-GEe7's avatar
V-GEe7
Brass Contributor
Apr 08, 2024

Fcin144 ,

 

As the number of characters change you may also need to change the number in the formula, try the below, for the number of client emails

=(LEN(A1)-LEN(SUBSTITUTE(A1,"@","")))-(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"@dreyfus",""),"@bnymell","")))/8

  and the following formula for yes/no

=IF(((LEN(A1)-LEN(SUBSTITUTE(A1,"@","")))-(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"@dreyfus",""),"@bnymell","")))/8)>0,"Yes","No")

Resources