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:

1. No Spaces
2. Must have 1 and only 1 @ symbol
3. The @ symbol cannot be the first character
4. Must have at least 1 . after the @ symbol
5. Must have at least 1 character between the @ and the .
6. 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.