Custom data validation

[Nichole09]

I am hoping I am on the right track, but the formula below does not work when entering this in custom data validation. I am attempting to create a code that will notify the user if they have entered multiple email address's in one cell without separating them out with a semicolon. I have entered the input message advising the user to do this, but I want to create an error if the user failed to do this. I want my formula to recognize if there are two ".com" or two email addresses (I figured the formula could just check if there are two ".com" would be the easiest solution) and if there is without a ; in between them then the following error message appears: Each email address must be separated with a semicolon ( ; )

=if(countif(V5,".com")>1,substitute(".com",".com;"))

I am not sure how to successfully se countif and substitute together. Any help is greatly appreciated.

 

[n1lesh]

=IF(SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,".com","")))>4,TRUE)

Change your cell references as required (looks for two .com)

 

[Nichole09]

Hi n1lesh thank you for your quick reply. I tried this but it is showing an error message with only one email address showing. I attempted to put two email addresses in that cell without a semicolon and it did not give me the error message. It seems to be doing the opposite. Can you help with modifying this? I want the error to appear only if there are two .com AND if that .com is not separated out with a semicolon. For example: Heythere@yhoo.com;thankagain@google.com only this entry would be acceptable (if two email addresses existed)

 

[mikerickson]

Try

=SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1,".com", " "),";"," "))," ",".com;")&".com"

 

[Nichole09]

Hi Mike! Thank you for your reply I tried this formula and it does not seem to work. the error appears with only one email address entered. I want it to show up if there are two email addresses and only if there is not a semicolon between them. I tried this code with entering two email address in my acceptable format: abc@yahoo.com;thanksalot@gmail.com and the error still pulls up. I would like the error message to only pop up if there are two email addresses entered in the cell and if a semicolon is not between the two email addresses.

 

[jtakw]

Hi,

Firstly, I'm Not so sure you want to use ".com" as the unique identifier, since there're Many different types of domains (i.e. .com, .net, .org, .edu, .gov, etc.)
So I'm using "@" which is used for Every type of email address.

Try this for your DV formula rule:

Book1
	A	B	C
1	abc@abc.com ;xyz@xyz.net		TRUE
2	abc@abc.com [email]xyz@xyz.net[/email]		FALSE
3	abc@abc.com ;xyz@xyz.net [email]ghi@ghi.org[/email]		FALSE
Sheet365

Cell Formulas
Range	Formula
C1	=LEN(SUBSTITUTE(A1,";",""))=LEN(SUBSTITUTE(A1,"@",""))+1

 

[Nichole09]

jtakw..... Genius! I didn't even think of the different domains and this code works perfectly!!! Thank you so much!

 

[jtakw]

You're very welcome, glad it worked for you.

BTW, the DV rule will work for Any number of email addresses in the cell, Not limited to 2.