Excel电子邮件验证公式

Question

我有一个列，人们手动输入电子邮件地址。我想使用这个公式验证电子邮件地址：

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

但 Excel 弹出错误提示，表明你输入的公式包含错误。对我来说，这个公式看起来是正确的。你们有什么建议吗？

- Hassan Ata Ullah

---

测试时它运行良好...不过我必须将 “ 字符更改为 ". - John Bustos

顺便提一下：允许使用空格，请参见http://en.wikipedia.org/wiki/Email_address#Local_part - Matteo

@Matteo，你通过编辑破坏了问题的意思...((( - Peter L.

@PeterL. 当然不是：问题不在于引号，而在于公式返回的是true还是#VALUE! - Matteo

@Matteo “但是Excel会出现错误，指出你输入的公式存在错误。” - 这不是单元格中的值，而是Excel消息框 - 尝试输入原始公式或使用“非纯文本”引号 - 你会得到相同的结果。额外/缺少的括号也会导致这种情况发生。 - Peter L.

Answer 1

我在你的代码中遇到了同样的错误，似乎你没有使用“普通”的双引号，而是使用了不同的符号:"。

尝试我的拼写: =AND(FIND("@",A2),FIND(".",A2),ISERROR(FIND(" ",A2))) - 希望可以帮到你!

编辑:

此外，考虑使用=AND(NOT(ISERROR(FIND("@",A1))),NOT(ISERROR(FIND(".",A1))),ISERROR(FIND(" ",A1)))，这将防止在缺少@或.时出现错误。尽管这仍将通过aaa@.作为OK，但我认为即使采用这样直接的方法也有权被使用）

- Peter L.

---

准确来说，你的双引号具有代码147和148（使用CODE函数进行检查），而“plain”是具有代码34的字符。 - Peter L.

@PeterL。如果没有“@”，第一个FIND函数将返回#VALUE!，您也必须将其包装起来。 - Matteo

@PeterL。拿你在Dropbox上放的例子，试着输入一个无效的地址，比如“aaa”。你会看到问题。 - Matteo

以防其他德国人想要使用这个公式 - 这里是德语版本：=UND(NICHT(ISTFEHLER(FINDEN("@"; A2))); NICHT(ISTFEHLER(FINDEN("."; A2))); ISTFEHLER(FINDEN(" "; A2))) - Olaf

=AND(NOT(ISERROR(FIND("@";A1)));NOT(ISERROR(FIND(".";A1)));ISERROR(FIND(" ";A1))) - Sedat Kumcu

Answer 2

在Excel中验证电子邮件的另一种方法是使用VBA代码：请参见下面的代码，取自http://www.vbaexpress.com/kb/getarticle.php?kb_id=281，它本身效果很好，您也可以根据自己的需求修改代码。

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 

如需说明，请查看http://www.vbaexpress.com/kb/getarticle.php?kb_id=281#instr

- Joel

Answer 3

我遇到了一个关于firstname.lastname@domain@topdomain 的问题，我使用一个隐式的Like修正了它并检查了正确的@和.的顺序。

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

编辑
"*?@?*.??*" 看起来更加描述性，只要顶级域名至少有两个字符长（截至本帖发布时，它们是这样的）。

- user3819867

---

这是一个公正的答案，对于基本的电子邮件似乎有效。但是对于更高级的电子邮件呢？像这样格式的电子邮件：[e-mail] 或 [e-mail]，我会得到一个错误的负面结果。 - Djinné22

Answer 4

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

这将验证“.”在“@”之后，这在已接受的答案中未经测试。

- dariogriffo

Answer 5

受 Joel 的解决方案启发，但更短。执行相同的检查：

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

- Le Droid