Applies To: SharePoint 2010
The column validation feature of SharePoint 2010 is pretty awesome but it can be relatively basic when compared to something like regular expressions. Yesterday I posted about validating phone number columns in SharePoint. Another common request is email addresses.
Proper validation of email addresses can be extremely complicated (just check out the Syntax section of the Wikipedia article). I’m sure you can get extra crazy with it and get it even closer to the actual specification, but for my needs some basic validation is all I’m really looking for.
The basic rules I’m enforcing are:
- No Spaces
- Must have 1 and only 1 @ symbol
- The @ symbol cannot be the first character
- Must have at least 1 . after the @ symbol
- Must have at least 1 character between the @ and the .
- The . cannot be the last character
The formula to do that 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])
)
)
)
)
To get this working in SharePoint, just copy the above and do a find and replace on [Email] with whatever your column is named. SharePoint will remove all the extra line breaks as soon as you save it.
What’s Happening
Column Validation works by returning true or false. Starting our formula with an AND statement allows us to pass multiple conditions (also returning true or false) and will return true only if all the conditions return true. This allows us to do multiple checks and ensure they all validate correctly.
The first check in Line 2 uses the FIND function to check for spaces. The ISERROR function wrapper returns true for any errors found. Since the FIND function returns an error if the string is not found, we’ll get a true for this check only when there are no spaces. This takes care of rule #1.
The second check in line 3 searches for an @ symbol using the FIND function beginning with the 2nd character. This ensures the @ symbol is not the first character (rule #3).
Having found the @, we put a second AND statement in line 5 to check some things concerning the @ we just found. The check in line 6 uses a FIND to look for another @ symbol starting with the character after (+1) where we found the first one. This takes care of rule #2.
Next we check for a period after the @ symbol in line 7. We do something similar to the above check except that we start with the 2nd character after (+2) where we found the @ symbol. This ensures there is at least 1 character between the @ and the period (rule #5) while making sure there is at least 1 period (rule #4).
Now that we’ve established there is a period after the @ we make sure that the location of that period is less than the length of the whole string using the LEN function. This makes sure the last character is not the period (rule #6).
There are several holes here which could be corrected by complicating the formula quite a bit (I’d be happy to have suggestions in the comments), but for 98% of all entries this is going to be sufficient. If this is for a public facing form you’ll probably want to invest more time in increasing the complexity of this formula, but for your normal internal sites this should be more than good enough.
Just open your list settings and edit/add your email column and expand the column validation section and paste the formula from above in there:
Side Note: The above formula will automatically make this a required column since the validation doesn’t allow blank columns. An easy fix for this is to wrap the above formula in an OR statement with an ISBLANK function. So something like this:
=OR(ISBLANK([YourColumn]),AndStatementFromAbove)
More information and a full example can be found on my Requirement Groups entry.
The Chris Kent 
[…] The Chris Kent Quick Fixes, Workarounds, & Neat Tricks I felt like sharing HomeAbout RSS ← Validate Email Address Columns in SharePoint […]
[…] For instance, I posted a while back about how to use SharePoint column validation to validate email addresses and phone numbers. Those are still good techniques, but by using an InfoPath list item form […]
HI, I’ve tried to use your formula in the column settings on a Office365 environment, and it doesn’t work. Get an error message when trying to save.
replace “yourcolum” in the OR statement with “Email” / your field name
Great post… Can this work for multiple email addresses separated by semi-colon? Any ideas how to make that work
works superb. love ur solution. :).Saved my day.
Awesome!!!! That really works!!! Thank you!!!
Very usefull.. If you need this formula in Sharepoint installed in Portugues-Brasil, here is the translated definition
=E(
ÉERROS(LOCALIZAR(” “;[Email];1));
SE(ÉERROS(LOCALIZAR(“@”;[Email];2));
FALSO;
E(
ÉERROS(LOCALIZAR(“@”;[Email];LOCALIZAR(“@”;[Email];2)+1));
SE(ÉERROS(LOCALIZAR(“.”;[Email];LOCALIZAR(“@”;[Email];2)+2));
FALSO;
LOCALIZAR(“.”;[Email];LOCALIZAR(“@”;[Email];2)+2) < NÚM.CARACT([Email])
)
)
)
)
Here ist the german translation for german sharepoint-installations:
=UND(
ISTFEHLER(FINDEN(” “; [Email];1));
WENN(ISTFEHLER(FINDEN(“@”; [Email];2));
FALSCH;
UND(
ISTFEHLER(FINDEN(“@”;[Email]; FINDEN(“@”; [Email];2)+1));
WENN(ISTFEHLER(FINDEN(“.”; [Email]; FINDEN(“@”; [Email];2)+2));
FALSCH;
FINDEN(“.”; [Email]; FINDEN(“@”; [Email];2)+2) < LÄNGE([Email])
)
)
)
)
Great info, thank you.
How could I alter this to ensure that email addresses are all from a specific domain (ie: @gmail.com)?
=AND(ISERROR(FIND(” “,Email,1)),IF(ISERROR(FIND(“@gmail”,Email,2)),FALSE,AND(ISERROR(FIND(“@gmail”,Email,FIND(“@gmail”,Email,2)+1)),IF(ISERROR(FIND(“.com”,Email,FIND(“@gmail”,Email,2)+2)),FALSE,FIND(“.com”,Email,FIND(“@gmail”,Email,2)+2)<LEN(Email)))))
(replace gmail with your site and com with whichever extension you use)
I’m using the below and it passes the SharePoint validation proofing, but it is not working on the actual form:
=OR(ISBLANK([Email]),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]))))))
This actually works fine in excel, but I can’t seem to make it work in SharePoint.
Is there anything that I may have missed?
I greatly appreciate your help. Thank you!
Hi!
Here is the Norwegian version of the validation:
=OG(
ERFEIL(FINN(” “; [Email];1));
HVIS(ERFEIL(FINN(“@”; [Email];2));
USANN;
OG(
ERFEIL(FINN(“@”;[Email]; FINN(“@”; [Email];2)+1));
HVIS(ERFEIL(FINN(“.”; [Email]; FINN(“@”; [Email];2)+2));
USANN;
FINN(“.”; [Email]; FINN(“@”; [Email];2)+2) < LENGDE([Email])
)
)
)
)
Thank you, just what I needed and worked PERFECTLY.
Thanks a lot this worked Perfectly, And below is how you can use this in Field Definition.
=OR(ISBLANK([Email]),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]))))))