## Forum Discussion

Fcin144

Apr 06, 2024Copper Contributor

# 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 ...

V-GEe7

Brass Contributor

Fcin144 , Try using the below formula to actually give you a number showing how many client emails are there.

`=(LEN(A1)-LEN(SUBSTITUTE(A1,"@","")))-(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"@abc.","."),"@nbc.",".")))/4`

However if you want only yes or no then use the below.

`=IF(((LEN(A1)-LEN(SUBSTITUTE(A1,"@","")))-(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"@abc.","."),"@nbc.",".")))/4)>0,"Yes","No")`

PFA the file for your reference.

Fcin144

Apr 07, 2024Copper Contributor

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.

- LorenzoApr 07, 2024Silver Contributor
in

**B2**:`=IF( SUM( IFERROR( SEARCH("@", TEXTSPLIT(A2, {"@bnymellon.com","@dreyfus.com"},,,1)), 0) ), "Yes", "No")`

- V-GEe7Apr 08, 2024Brass Contributor
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")`