Excel电子邮件验证公式

Question

我有一列，人们可以在其中手动输入电子邮件地址。我想使用以下公式验证电子邮件地址：

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

但是Excel出现错误，表明您键入的公式包含错误。对我来说，公式看起来是正确的。你们有什么建议吗？

Answer 1

我的代码也遇到了同样的错误，而且您似乎没有"纯"双引号，这与这个符号不同：".

试试我的拼写：=AND(FIND("@",A2),FIND(".",A2),ISERROR(FIND(" ",A2))) - 希望会有所帮助！

编辑：

此外，请考虑使用=AND(NOT(ISERROR(FIND("@",A1))),NOT(ISERROR(FIND(".",A1))),ISERROR(FIND(" ",A1))) - 这将防止在缺少@或.时出错。尽管如此，这将通过 可以aaa@. ，但我想即使是这样简单的方法也有权使用）

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

Answer 3

我遇到了一个firstname.lastname@domain@topdomain问题，为此我进行了修改，使用没有 VBA 的隐式Like检查@和.的正确顺序。

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

编辑
只要顶级域名至少有两个字符长（截至本文），"*?@?*.??*"似乎更具描述性。

Answer 4

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

这将验证 . 是在未在接受的答案上测试的 @ 之后

Answer 5

灵感来自乔尔的解决方案，但更短。执行相同的检查：

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