Disallow special characters except '@' in email validation formula

0
votes

I have this formula which validates the email address for Excel:

=AND(ISERROR(FIND(" ",[Send Report To],1)),IF(ISERROR(FIND("@",[Send Report To],2)),FALSE,AND(ISERROR(FIND("@",[Send Report To],FIND("@",[Send Report To],2)+1)),IF(ISERROR(FIND(".",[Send Report To],FIND("@",[Send Report To],2)+2)),FALSE,FIND(".",[Send Report To],FIND("@",[Send Report To],2)+2)<LEN([Send Report To])))))

But fails when I enter this email id: sandy,[email protected] or sandy.rocks@gmail,co.uk.

Can anyone amend the validation formula above so that it avoids the special characters like commas within the email address?

3
excelemailexcel-formulaexcel-2010excel-2007
Avoid means if it has commas (,), dolor sign ($) and other special characters which is not valid for e-mail address then the formula will give out "False", otherwise "TRUE". Is my assumption is right? - Harun24HR
that's right.Best thing would be to validate after the @ symbol. - Sandy W

3 Answers

1
votes

Please look this formula. You can add more special character in or argument. I just added "!,@,#,$,%,^" these characters.

=IF(OR(ISNUMBER(SEARCH(",",A1)),ISNUMBER(SEARCH("$",A1)),ISNUMBER(SEARCH("!",A1)),ISNUMBER(SEARCH("%",A1)),ISNUMBER(SEARCH("^",A1)),ISNUMBER(SEARCH("#",A1)),ISNUMBER(SEARCH("@",A1,SEARCH("@",A1)+1))),FALSE,TRUE)

Screenshot

enter image description here

0
votes

I think this is difficult with a built-in formula

Instead, a UDF with regex is suitable to ensure email pattern matching.

Refer answer to this question https://stackguides.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

Very well explained with details and examples. Do read that post.

Based on that answer, here is the UDF I came up with

Function simpleCellRegex(Myrange As Range) As Boolean
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String

    strPattern = "^[a-z][a-z0-9\._]+@[a-z]+\.[a-z]+\.?[a-z]+$"


    If strPattern <> "" Then
        strInput = LCase(Myrange.Value)

        With regEx
            .Global = True
            .MultiLine = False
            .IgnoreCase = True
            .Pattern = strPattern
        End With

        simpleCellRegex = regEx.Test(strInput)

    End If

    Set regEx = Nothing
End Function
0
votes

The answer is as per Haruns suggestion above. This works indeed

=IF(OR(ISNUMBER(SEARCH(",",[Send Report To])),ISNUMBER(SEARCH("$",[Send Report To])),ISNUMBER(SEARCH("!",[Send Report To])),ISNUMBER(SEARCH("%",[Send Report To])),ISNUMBER(SEARCH("^",[Send Report To])),ISNUMBER(SEARCH("#",[Send Report To])),ISNUMBER(SEARCH("@",[Send Report To],SEARCH("@",[Send Report To])+1))),FALSE,TRUE)