Formula to verify properly formed email address with no additional characters or blanks

[Number1]

Does anyone have a canned Excel formula that will check a cell to confirm it is a properly formed email address with no extra characters or blanks?


In cell A1 might be:
name@domain.com
first@anydomain.org
or any similar properly formed email address
Formula in cell B1 would return FALSE

In cell A1 might be:
name@domain.com;
name@domain.com (trailing blank)
first@anydomain.org-
first@anydomain.org (leading blank)
or any similar malformed email address
Formula in cell B1 would return TRUE

Thanks!!!!!

 

[Michael M]

This should return TRUE if the email address is correct and FALSE otherwise

=ISNUMBER(MATCH("*@*.???",A1,0))

 

[Number1]

Michael M---
Good start but there are some cases where it fails:
first@anydomain.org (leading blank)
;first@anydomain.org
first@anydomain.us
first@anydomain.us;

Thoughts on ways to address these cases?

Thanks!!!

Any thou

 

[Michael M]

I'm guessing there will be quite a few options where it fails.
Have you considered recreating the email address in the adjacent column correctly??

Something like

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),";",""),"-","")