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?

... works fine when I tested it... I had to change the `“` characters to `"`, though... — John Bustos, Jan 23 '13 at 15:08
On a side note: a space is allowed: see http://en.wikipedia.org/wiki/Email_address#Local_part — Matteo, Jan 23 '13 at 15:12
@Matteo by editing you RUINED the meaning of the question...((( — Peter L., Jan 23 '13 at 15:26
@PeterL. Of course not: the problem was not the quotes but the fact that the formula is returning either true or #VALUE! — Matteo, Jan 23 '13 at 15:28
You say you are validating the addresses - how exactly? Are you using the formula in a cell on the worksheet or within Data Validation? — barry houdini, Jan 23 '13 at 15:29
@Matteo "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. — Peter L., Jan 23 '13 at 15:31

Peter L. · Accepted Answer · 2013-01-23T15:44:51.743

24

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)

edited Jan 23 '13 at 15:44

answered Jan 23 '13 at 15:09

Peter L.

7,276
5
34
53

To be precise, your double quotes have codes 147 and 148 (use `CODE` function to check), while "plain" is char with code 34. – Peter L. Jan 23 '13 at 15:12
Thanks for the answer but I got the same error when i copied the formula u provided. – Hassan Ata Ullah Jan 23 '13 at 15:14
@HassanAtaUllah Seems strange - check my sample file: https://www.dropbox.com/s/gavgg48733m3918/MailCheck.xlsx – Peter L. Jan 23 '13 at 15:18
Does not work as FIND will return an error if nothing if found – Matteo Jan 23 '13 at 15:22
@Matteo that's why formula has `ISERROR` wrapper! check my sample file in the above comment - it works fine. – Peter L. Jan 23 '13 at 15:24
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.... – barry houdini Jan 23 '13 at 15:24
@PeterL. If there is no "@" the first `FIND` will return `#VALUE!` you have to wrap it too – Matteo Jan 23 '13 at 15:25
@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. – Peter L. Jan 23 '13 at 15:29
@PeterL. Take the example you put on Dropbox and try to enter an invalid address like "aaa". You will see the problem – Matteo Jan 23 '13 at 15:30
ah then the problem is solved thx alot for the help peter L :) – Hassan Ata Ullah Jan 23 '13 at 15:30
@PeterL. I didn't notice that formula was used for validation and that #VALUE is OK. I reverted the edit on the question – Matteo Jan 23 '13 at 15:32
@Matteo updated sample file and added more correct version to my post. Thanks, that was useful! – Peter L. Jan 23 '13 at 15:44
2

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))) – Olaf Aug 06 '13 at 09:09
Turkish version is below. replace comma to semicolon(;) =AND(NOT(ISERROR(FIND("@";A1)));NOT(ISERROR(FIND(".";A1)));ISERROR(FIND(" ";A1))) – Sedat Kumcu Apr 08 '15 at 14:16
a.a@a is valid with this answer. Another possible solution =AND(IFERROR(FIND(".",A2),FALSE),IFERROR(FIND(".",A2,FIND("@",A2)),FALSE)) – dariogriffo Jan 25 '17 at 11:26

score 12 · Answer 2 · edited Sep 26 '14 at 16:14

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

user3819867 · Answer 3 · 2015-08-26T13:51:33.000

4

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).

edited Aug 26 '15 at 13:51

answered Aug 26 '15 at 13:39

user3819867

1,114
1
8
18

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: bob.jones@foobar.com.au OR bob.jones@foobar.com – Djinné22 Sep 22 '15 at 22:44

score 1 · Answer 4 · answered Jan 25 '17 at 11:27

1

=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

answered Jan 25 '17 at 11:27

dariogriffo

4,148
3
17
34

score 0 · Answer 5 · answered Mar 05 '23 at 15:34

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

excel email validation formula

5 Answers5

Linked

Related