SOLVED

# Identify if multiple values exist in one cell

Copper 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 other than the above (@nbc.com or @abc.com) in cell A2 and A3

I tried this formula =IF(OR(COUNTIF(A3,{"*nbc*","*abc*"})),"No","Yes")

but it returned "No". The cell has @abc.com listed in the cell and  I would like it to return "Yes".

Thank you

14 Replies

# Re: Identify if multiple values exist in one cell

@Fcin144 What version of Excel are you using? With Excel for MS365 or Excel for the Web you could try the following:

=IF(OR(ISNA(XMATCH(TEXTAFTER(TEXTSPLIT(A2, ","), "@"), {"abc.com","nbc.com"}))), "Yes", "No")

You could also use the MAP function to "spill" the results for the entire list of email addresses. For example:

=MAP(A2:A10, LAMBDA(v, IF(OR(ISNA(XMATCH(TEXTAFTER(TEXTSPLIT(v, ","), "@"), {"abc.com","nbc.com"}))), "Yes", "No")))

Sample Results

# Re: Identify if multiple values exist in one cell

With older version of Excel (but on Mac)

=IF(
SUM( IFERROR( SEARCH({"@abc.com","@nbc.com"}, B3)^0, 0) )
<> COUNTA( FILTERXML("<t><w>" & SUBSTITUTE(B3, ",", "</w><w>") & "</w></t>", "//w") ),
"Yes", "No"
)

# Re: Identify if multiple values exist in one cell

Thank you for the reply. I am using Web Version 365.

I tried your recommended statement however it returned No when either abc.com or nbc.com was found. In addition, when any other email address was found it returned Yes.

=IF(OR(ISNA(XMATCH(TEXTAFTER(TEXTSPLIT(A2, ","), "@"), {"abc.com","nbc.com"}))), "Yes", "No")

I would like the Client Field in the spreadsheet that stores the cell and the statement to return "Yes" when any other email address besides abc.com or nbc.com is found using the statement.

Thanks again in advance.

# Re: Identify if multiple values exist in one cell

Thank you for the reply. I am using Web Version 365.

I tried your recommended statement however it didn't work.

I would like the Client Field in the spreadsheet that stores the cell and the statement to return "Yes" when any other email address besides abc.com or nbc.com is found using the statement.

Just an fyi, the field "B3" in your example is part of a table. I am not sure if that changes anything.

Thanks again in advance.

# Re: Identify if multiple values exist in one cell

I would like ...to return "Yes" when any other email address besides abc.com or nbc.com is found:

in C3:

=IF( SUM( IFERROR( SEARCH("@", TEXTSPLIT(B3, {"@abc.com","@nbc.com"})), 0) ), "Yes", "No")

Works better/does it?

# Re: Identify if multiple values exist in one cell

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

# Re: Identify if multiple values exist in one cell

@Fcin144 wrote:

I tried your recommended statement however it returned No when either abc.com or nbc.com was found.

The formula only returns "No" when ALL email addresses in the cell are company accounts (ALL domain names are either "abc.com" or "nbc.com").

@Fcin144 wrote:

In addition, when any other email address was found it returned Yes.

...

I would like the Client Field in the spreadsheet that stores the cell and the statement to return "Yes" when any other email address besides abc.com or nbc.com is found using the statement.

That's exactly what the formula does... when any non-company email address is found, it returns "Yes".

I'm afraid your question is unclear. What you want appears to conflict with what you don't want. It would help if you could provide a larger sample set that accurately and completely reflects your actual data, showing what the formula is currently returning for each scenario, plus a third column showing the expected results (manually type what the result should be and why).

# Re: Identify if multiple values exist in one cell

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.

best response confirmed by Fcin144 (Copper Contributor)
Solution

# Re: Identify if multiple values exist in one cell

in B2:

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

# Re: Identify if multiple values exist in one cell

Is the data in column A actual data? as in a value or is it a formula to populate the cell? if its not a value you will find it hard to get a formula to recognize the data - unless you put value at the front. eg. if(value(or(countif(a3,{"*nbc*","*abc*"})),"No","Yes")

# Re: Identify if multiple values exist in one cell

For Column A, it actual data, it is a value. It is not a formula.

# Re: Identify if multiple values exist in one cell

Thank you soooooo much for your patience and expertise, it is truly appreciated. It worked !!!!

# Re: Identify if multiple values exist in one cell

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")

# Re: Identify if multiple values exist in one cell

Thanks for providing feedback
1 best response

Accepted Solutions
best response confirmed by Fcin144 (Copper Contributor)
Solution

# Re: Identify if multiple values exist in one cell

in B2:

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