Email Address Validation

[snd]

Hi,
I came across this formula that validates an email address:

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

It works fine if the "@" or/and dot is missing but it doesn't check for these rules:
- the "@" comes 1st then the dot,
- the "@" and the "." are separated (don't come together)
- Neither of them come as a prefix or as a suffix
- other rules.

I was just wondering, if it's possible- to at least- validate an email address for the previous rules.

Thanks to you all gurus.
(I'm using Excel 2013).

 

[Joe4]

Sure. You can design your own Custom Function in VBA to test all the you want.

 

[Joe4]

Here is a UDF that does most of those things. You can build off of it to add whatever else you need:

Function ValidateEmail(emailAddr As String) As Boolean

    Dim findAt As Long
    Dim findLastDot As Long
    Dim findSpace As Long
    
'   Default validate is true
    ValidateEmail = True
    
    findAt = InStr(emailAddr, "@")
    findLastDot = InStrRev(emailAddr, ".")
    findSpace = InStr(emailAddr, " ")
    
'   Check for characters
    If (findAt = 0) Or (findLastDot = 0) Or (findSpace > 0) Then
        ValidateEmail = False
        Exit Function
    End If
    
'   Make sure amperand not first space
    If findAt = 1 Then
         ValidateEmail = False
        Exit Function
    End If
    
'   Make sure last dot comes at least two spots after ampersand
    If (findLastDot - 1) <= findAt Then
        ValidateEmail = False
        Exit Function
    End If
        
End Function

So, you would just use it like any other Excel function.
For example, if you had an email address in cell A1 and you wanted to see if it was valid, you would write the formula:
=ValidateEmail(A1)
and it will either return TRUE (if good email address) or FALSE (if not good email address, based upon our rules).

 

[snd]

Thanks a lot Joe4 for your help, it works just fine.

 

[Joe4]

You are welcome!