Validate Email Format. Check format of email address

To check if email format is correct

1. Copy the code below.
2. Open any workbook.
3. Press Alt + F11 to open the Visual Basic Editor (VBE).
4. Press Ctrl + R to show the Project Explorer.
5. Right-click desired file on left (in bold).
6. From the Menu, choose Insert-Module.
7. Paste the code into the right-hand code window.
8. Close the VBE, save the file  as VBA Enabled WorkBook (xlsm) for Excel 2010 and newer

How to Use the UDF

1. Make sure you have enabled the macros
2. You can use this in a formula on your worksheet as follows =IsEmailValid("someemail@somedomain.com")
3. If the email address is valid, the formula will return TRUE

https://www.quora.com/Microsoft-Excel-There-is-a-spreadsheet-with-thousands-of-email-addresses-in-one-column-with-a-single-address-per-cell-One-email-address-is-false-e-g-contains-a-space-or-an-unusable-letter-symbol-What-process-would-you-use-to-easily-find-it

 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