Disallow special characters except '@' in email validation formula

Question

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,rocks@gmail.com 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?

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, Nov 25 '15 at 14:44
that's right.Best thing would be to validate after the @ symbol. — Sandy W, Nov 25 '15 at 14:59

score 1 · Answer 1 · answered Nov 26 '15 at 03:42

1

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

answered Nov 26 '15 at 03:42

Harun24hr

30,391
4
21
36

Hi Harun, Great that works...on office 365 column validation...You are a star. – Sandy W Nov 26 '15 at 17:14
Thanks dear. Excel is really fantastic and I enjoy it. – Harun24hr Nov 27 '15 at 03:08

score 0 · Answer 2 · edited May 23 '17 at 10:28

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://stackoverflow.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

Thanks for your reply. Actually i need to validate a column in sharepoint online site using office 365.Fact is that the validation of the column is as same as built on formula for excel. — Sandy W, Nov 25 '15 at 20:11

score 0 · Answer 3 · answered Nov 26 '15 at 17:17

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)

Disallow special characters except '@' in email validation formula

3 Answers3