Email address validation error

Question

I have an EMAIL column and added this validation code:

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

as suggested here :

Validate Email Address Columns in SharePoint

Unfortunately I get an error message

The formula contains a syntax error or is not supported - See more at: http://sharepointknowledgebase.blogspot.it/2011/02/formula-contains-syntax-error-or-is-not.html#.VL5KwXl8OUk

Answer 1

Most probably this error occurs since locale for web site is set to non-default language (1033) and the incorrect delimiter is specified for a function (in your case the delimiter is ,) The delimiter in formula is language dependent.

For example when web locale is set to 1035 (Finnish) then the proper syntax for formula is:

=AND(ISERROR(FIND(" ";Email;1));IF(ISERROR(FIND("@";Email;2));FALSE;AND(ISERROR(FIND("@";Email;FIND("@";Email;2)+1));IF(ISERROR(FIND(".";Email;FIND("@";Email;2)+2));FALSE;FIND(".";Email;FIND("@";Email;2)+2)<LEN(Email)))))

Note: delimiter ; is used instead of ,

Solution

• Determine the web locale (go to Site Settings -> click Regional settings under Site Administration group and find Locale for the current web site)

• Make sure the proper syntax for the specified locale is used (use the below links)

---

You could also consider another approach (more advanced i would say) to validate email field as explained in this article. The idea is to apply client-side validation for a text field using regular expression.

References

• Overview of formulas and functions
• A list of available functions

Answer 2

Use the following JS.

    function IsValidEmail(Email) {

        var expr = /^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$/;

        return expr.test(Email);

    };

    function ValidateEmails() {

        var message = ""

        var inputs = document.getElementsByTagName("input");

        for (var i = 0; i < inputs.length; i++) {

            if (inputs[i].type == "text" && inputs[i].className.indexOf("Email") != -1) {

                var email = inputs[i].value;

                if (IsValidEmail(Email)) {

                    message += @ + " - Valid Email.\n"

                } else {

                    message += @gmail + " - Invalid Email.\n"
                    message += @yahoo + " - Invalid Email.\n"

                }

            }

        }

        alert(invalid email address);

    }