How do I make an Excel Data Validation to check specifics about E-mail address formats??

[Moho280]

I've been asked to make an excel book for my job, that has a data validation in it to check e-mail addresses. They want the validation to check for a few things, and I can't get it to work for all of them at once. When someone enters an e-mail into the cell, they want excel to check for:

-The email to contain at least one period (.)
-The email to contain one 'at' symbol (@), BUT no more than one @ symbol

So far I getting Excel to check that the '.' and '@' are present hasn't been a problem since I've used the formula before, but I can't get the error message to show for if there's more than one @ symbol. I'm using this formula:

=AND(FIND(".",A1),FIND("@",A1))

Any ideas on this? Thanks in advance!

[benishiryo]

hi Moho280, welcome to the forum. try:
=AND(FIND(".",A1),SUMPRODUCT(--(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="@"))=1)

[vlady]

just to post using len()

Please Login or Register  to view this content.

[Moho280]

Thanks a ton, both answers were a huge help!! Very much appreciated :D

[thelightkeeper]

Sorry but all answers are incompleted !

tested with: "[email protected]" --> return TRUE (it's wrong, this is not a valid email)