excel email validation formula

Question

I have a column where people enter email address manually. I want to validate the email address using this formula:

=AND(FIND(“@”,A2),FIND(“.”,A2),ISERROR(FIND(” “,A2)))

but excel comes up with error that the formula you typed contains an error. For me the formula looks right. Do you guys have any suggestions?

Goosander

... works fine when I tested it... I had to change the “ characters to ", though... – Territory

Are you really using “” and not ""? – Stepsister

On a side note: a space is allowed: see en.wikipedia.org/wiki/Email_address#Local_part – Stepsister

I re-edited the question with correct quotes (") – Stepsister

@Stepsister by editing you RUINED the meaning of the question...((( – Clemmer

@PeterL. Of course not: the problem was not the quotes but the fact that the formula is returning either true or #VALUE! – Stepsister

You say you are validating the addresses - how exactly? Are you using the formula in a cell on the worksheet or within Data Validation? – Catechol

@Stepsister "but excel comes up with error that the formula you typed contains an error." - that's NOT a value in cell, but Excel message box - try to type original formula or simply use "not plain" quote - you'll get the same. Extra / missing bracket will come up to this as well. – Clemmer

@PeterL. Yes I see now ... Reverted the edit – Stepsister

Answer 1

I got the same error for your code, and it appears that you have NOT "plain" double quotes, that is different from this symbol: ".

Try my spelling: =AND(FIND("@",A2),FIND(".",A2),ISERROR(FIND(" ",A2))) - hope will help!

EDIT:

In addition, consider to use =AND(NOT(ISERROR(FIND("@",A1))),NOT(ISERROR(FIND(".",A1))),ISERROR(FIND(" ",A1))) - that will prevent errors in case @ or . are missing. Still, this will pass as OK aaa@., but I suppose even such straightforward approach has rights to be used)

Clemmer

To be precise, your double quotes have codes 147 and 148 (use CODE function to check), while "plain" is char with code 34. – Clemmer

Thanks for the answer but I got the same error when i copied the formula u provided. – Goosander

@HassanAtaUllah Seems strange - check my sample file: dropbox.com/s/gavgg48733m3918/MailCheck.xlsx – Clemmer

Does not work as FIND will return an error if nothing if found – Stepsister

@Stepsister that's why formula has ISERROR wrapper! check my sample file in the above comment - it works fine. – Clemmer

Are you using in "Data Validation"? If so then you may get a mesage saying "the formula currently evaluates to an error" but that doesn't mean you can't use it.... – Catechol

@PeterL. If there is no "@" the first FIND will return #VALUE! you have to wrap it too – Stepsister

@HassanAtaUllah "In your answer you use "," instead of ";"" - that's because of regional settings: my region uses ";" as separator in arguments, while other regions do use comma. – Clemmer

@PeterL. Take the example you put on Dropbox and try to enter an invalid address like "aaa". You will see the problem – Stepsister

ah then the problem is solved thx alot for the help peter L :) – Goosander

@PeterL. I didn't notice that formula was used for validation and that #VALUE is OK. I reverted the edit on the question – Stepsister

@Stepsister updated sample file and added more correct version to my post. Thanks, that was useful! – Clemmer

Just in case another German wants to use the formula - here is the German version: =UND(NICHT(ISTFEHLER(FINDEN("@"; A2))); NICHT(ISTFEHLER(FINDEN("."; A2))); ISTFEHLER(FINDEN(" "; A2))) – Newmodel

Turkish version is below. replace comma to semicolon(;) =AND(NOT(ISERROR(FIND("@";A1)));NOT(ISERROR(FIND(".";A1)));ISERROR(FIND(" ";A1))) – Conscientious

a.a@a is valid with this answer. Another possible solution =AND(IFERROR(FIND(".",A2),FALSE),IFERROR(FIND(".",A2,FIND("@",A2)),FALSE)) – Entoblast

Answer 2

Another way to validate emails in excel is using VBA code: see code below taken from http://www.vbaexpress.com/kb/getarticle.php?kb_id=281, it works great as is, and you can modify the code based on your needs.

Sub email() 
Dim txtEmail As String 
txtEmail = InputBox("Type the address", "e-mail address") 

Dim Situacao As String 

 ' Check e-mail syntax
If IsEmailValid(txtEmail) Then 
    Situacao = "Valid e-mail syntax!" 
Else 
    Situacao = "Invalid e-mail syntax!" 
End If 
 ' Shows the result
MsgBox Situacao 
End Sub 
Function IsEmailValid(strEmail) 
Dim strArray As Variant 
Dim strItem As Variant 
Dim i As Long, c As String, blnIsItValid As Boolean 
blnIsItValid = True 

i = Len(strEmail) - Len(Application.Substitute(strEmail, "@", "")) 
If i <> 1 Then IsEmailValid = False: Exit Function 
ReDim strArray(1 To 2) 
strArray(1) = Left(strEmail, InStr(1, strEmail, "@", 1) - 1) 
strArray(2) = Application.Substitute(Right(strEmail, Len(strEmail) - Len(strArray(1))), "@", "") 
For Each strItem In strArray 
    If Len(strItem) <= 0 Then 
        blnIsItValid = False 
        IsEmailValid = blnIsItValid 
        Exit Function 
    End If 
    For i = 1 To Len(strItem) 
        c = LCase(Mid(strItem, i, 1)) 
        If InStr("abcdefghijklmnopqrstuvwxyz_-.", c) <= 0 And Not IsNumeric(c) Then 
            blnIsItValid = False 
            IsEmailValid = blnIsItValid 
            Exit Function 
        End If 
    Next i 
    If Left(strItem, 1) = "." Or Right(strItem, 1) = "." Then 
        blnIsItValid = False 
        IsEmailValid = blnIsItValid 
        Exit Function 
    End If 
Next strItem 
If InStr(strArray(2), ".") <= 0 Then 
    blnIsItValid = False 
    IsEmailValid = blnIsItValid 
    Exit Function 
End If 
i = Len(strArray(2)) - InStrRev(strArray(2), ".") 
If i <> 2 And i <> 3 Then 
    blnIsItValid = False 
    IsEmailValid = blnIsItValid 
    Exit Function 
End If 
If InStr(strEmail, "..") > 0 Then 
    blnIsItValid = False 
    IsEmailValid = blnIsItValid 
    Exit Function 
End If 
IsEmailValid = blnIsItValid 
End Function 

For how to instructions check http://www.vbaexpress.com/kb/getarticle.php?kb_id=281#instr

Shoeshine

Answer 3

I bumped into an issue of firstname.lastname@domain@topdomain for which I made an amendment that checks the correct order of the @ and the . with an implicit Like without VBA.

=AND(NOT(ISERROR(VLOOKUP("*@*.*",A2,1,FALSE))),ISERROR(FIND(" ",A2)))

EDIT
"*?@?*.??*" seems to be even more descriptive as long as top-level domains are at least two characters long (as of this post they are).

Unsupportable

This is a fair answer, and seems to work for basic emails. but what about more advanced emails? I get a false negative for emails with formats like: [email protected] OR [email protected] – Lukin

Answer 4

=AND(IFERROR(FIND(".",A2),FALSE),IFERROR(FIND(".",A2,FIND("@",A2)),FALSE))

This will validate the . is after the @ which is not tested on the accepted answer

Entoblast

Answer 5

Inspired from Joel's solution but shorter. Does the same checks:

Function IsEmailValid(strEmail) 
   Dim i As Integer, emailPart As Variant
   IsEmailValid = IsMadeOf(LCase(strEmail), "abcdefghijklmnopqrstuvwxyz0123456789.-_@")
   emailPart = Split(strEmail, ".")
   i = 0
   While IsEmailValid And i <= UBound(emailPart)
      IsEmailValid = Len(emailPart(i)) > IIf(i = UBound(emailPart), 1, 0)
      i = i + 1
   Wend
   If IsEmailValid Then
      emailPart = Split(strEmail, "@")
      IsEmailValid = UBound(emailPart) = 1 And InStr(emailPart(UBound(emailPart)), ".") > 0
   End If
End Function 

Function IsMadeOf(str, charList)
   Dim i As Long, c As String
   IsMadeOf = True
   For i = 1 To Len(str)
      c = Mid(str, i, 1)
      If InStr(charList, c) <= 0 Then
         IsMadeOf = False
         Exit Function
      End If
   Next i
End Function

Funerary