I generally validate email addresses in Filemaker and find this a much quick and easier solution. But I get asked a fair bit how would you be able to validate an email address in Excel.<\/p>\n
There are a couple of ways to do this, a quick and simple approach is to simply check if the cell is a hyperlink (i.e. Excel has done a check as it was entered) and then check for “@”.<\/p>\n
Sub IsValidEMailAddress_Simple()
\nRange(“A1”).Select
\nIf ActiveCell.Hyperlinks(1).Type = msoHyperlinkRange Then
\nintChar = InStr(1, ActiveCell.Hyperlinks(1).Address, “@”)
\nMsgBox ActiveCell.Value & ” not hyperlink”
\n‘Was the ‘@’ found?
\nIf intChar > 0 Then
\n‘Does hyperlink contain ‘@’?
\nMsgBox ActiveCell.Value & ” missing @”
\nEnd If
\nEnd If
\nEnd Sub<\/p><\/blockquote>\nThis generally isn’t ideal and so for a much more comprehensive check this code (an Excel VB function) will fully validate a referenced cell with an email address in as a true or false (boolean). There is a text file download at the end incase you get formatting issues with copy and paste.<\/p>\n
Public Function IsValidEMailAddress( _
\nByVal EMailAddress As String, _
\nOptional ByVal Strict As Boolean = False _
\n) As Boolean<\/p>\n‘ Return True if the email address referenced is valid, False otherwise.<\/p>\n
Const Domain_Extensions = “|aero|biz|com|coop|edu|gov|info|int|mil|museum|name|net|org|pro|travel|”
\nConst Country_Extensions = “|ac|ad|ae|af|ag|ai|al|am|an|ao|aq|ar|as|at|au|aw|ax|az|ba|bb|bd|be|bf|bg|bh|bi|bj|bm|bn|bo|br|bs|<\/p>\nbt|bv|bw|by|bz|ca|cc|cd|cf|cg|ch|ci|ck|cl|cm|cn|co|cr|cs|cu|cv|cx|cy|cz|de|dj|dk|dm|do|dz|ec|ee|<\/p>\n
eg|eh|er|es|et|eu|fi|fj|fk|fm|fo|fr|ga|gb|gd|ge|gf|gg|gh|gi|gl|gm|gn|gp|gq|gr|gs|gt|gu|gw|gy|hk|<\/p>\n
hm|hn|hr|ht|hu|id|ie|il|im|in|io|iq|ir|is|it|je|jm|jo|jp|ke|kg|kh|ki|km|kn|kp|kr|kw|ky|kz|la|lb|lc|li|<\/p>\n
lk|lr|ls|lt|lu|lv|ly|ma|mc|md|mg|mh|mk|ml|mm|mn|mo|mp|mq|mr|ms|mt|mu|mv|mw|mx|my|mz|<\/p>\n
na|nc|ne|nf|ng|ni|nl|no|np|nr|nu|nz|om|pa|pe|pf|pg|ph|pk|pl|pm|pn|pr|ps|pt|pw|py|qa|re|ro|ru|rw|<\/p>\n
sa|sb|sc|sd|se|sg|sh|si|sj|sk|sl|sm|sn|so|sr|st|sv|sy|sz|tc|td|tf|tg|th|tj|tk|tl|tm|tn|to|tp|tr|tt|tv|tw|<\/p>\n
tz|ua|ug|uk|um|us|uy|uz|va|vc|ve|vg|vi|vn|vu|wf|ws|ye|yt|yu|za|zm|zw|”
\nConst Invalid_Chars = “\/’\\””;:?!()[]{}^| ”
\nConst Invalid_Chars_Strict = “\/’\\””;:?!()[]{}^|$&*+=`<>,% ”
\nConst Invalid_Domains = “|aso|dnso|icann|internic|pso|afrinic|apnic|arin|example|gtld-servers|iab|iana|iana-servers|iesg|ietf|irtf|istf|lacnic|latnic|rfc -editor|ripe|root-servers|nic|whois|www|arpa|”<\/p>\nDim Index As Long
\nDim Extension As String
\nDim Domain As String
\nDim Position1 As Long
\nDim Position2 As Long<\/p>\nIf Len(EMailAddress) = 0 Then
\nIsValidEMailAddress = True
\nExit Function
\nEnd If<\/p>\nEMailAddress = LCase(EMailAddress)<\/p>\n
‘ Check for invalid characters
\nIf Strict Then
\nFor Index = 1 To Len(EMailAddress)
\nIf InStr(Invalid_Chars_Strict, Mid(EMailAddress, Index, 1)) > 0 Then
\nExit Function
\nEnd If
\nNext Index
\nElse
\nFor Index = 1 To Len(EMailAddress)
\nIf InStr(Invalid_Chars, Mid(EMailAddress, Index, 1)) > 0 Then
\nExit Function
\nEnd If
\nNext Index
\nEnd If<\/p>\n‘ Check for valid extension
\nIndex = InStrRev(EMailAddress, “.”)
\nIf Index = 0 Then Exit Function
\nExtension = Mid(EMailAddress, Index + 1)
\nIf InStr(Domain_Extensions, “|” & Extension & “|”) = 0 And InStr(Country_Extensions, “|” & Extension & “|”) = 0 Then Exit Function<\/p>\n‘ Check for consecutive dots
\nIf InStr(EMailAddress, “..”) > 0 Then Exit Function<\/p>\n‘ Check for more than one ampersand
\nIf InStr(Replace(EMailAddress, “@”, ” “, Count:=1), “@”) > 0 Then Exit Function<\/p>\n‘ Check for text prior to the ampersand
\nIndex = InStr(EMailAddress, “@”)
\nIf Not Index > 1 Then Exit Function<\/p>\n‘ Check for a period after the ampersand
\nIf Mid(EMailAddress, Index + 1, 1) = “.” Then Exit Function<\/p>\nPosition1 = InStr(EMailAddress, “@”) + 1
\nPosition2 = InStr(Position1, EMailAddress, “.”) – 1
\nDomain = Mid(EMailAddress, Position1, Position2 – Position1 + 1)<\/p>\nIf Strict Then
\n‘ Check for single character domain
\nIf Len(Domain) = 1 Then Exit Function
\n‘ Check for an invalid domain
\nIf InStr(Invalid_Domains, “|” & Domain & “|”) > 0 Then Exit Function
\nIf InStr(Domain_Extensions, “|” & Domain & “|”) > 0 Then Exit Function
\nEnd If<\/p>\n‘ Check for dash in the first, last, third, or fourth position of the domain
\nIf Left(Domain, 1) = “-” Then Exit Function
\nIf Right(Domain, 1) = “-” Then Exit Function
\nIf Len(Domain) > 2 Then
\nIf Mid(Domain, 3, 1) = “-” Then Exit Function
\nIf Len(Domain) > 3 Then
\nIf Mid(Domain, 4, 1) = “-” Then Exit Function
\nEnd If
\nEnd If<\/p>\n‘ Check for more then 67 characters in the domain and extension
\nIf Len(Domain) + Len(Extension) > 67 Then Exit Function<\/p>\nIsValidEMailAddress = True<\/p>\n
End Function<\/p><\/blockquote>\n