When creating a list in SharePoint 2013 sometimes it is useful to enforce data validation on column entries. One that I use regularly is Email validation. I have a set of rules that I want to apply to ensure that an entered email address is at least in the correct format, we can’t validate that the email address will work, but, we can at least make sure that the email address is in a valid format.
We do this using formulas in the column validation section of the column settings.
Open your list, and go to the List Settings.
Create your email column (if it isn’t already created).
Once it is there, click on the column name to edit the settings.
Then We need to open ‘Column Validation’
This will expand to show a formula window and a user message box.
We are going to enter our ‘formula’ into the first box. This code contains all of the rules that we are going to apply.
I have 6 rules:
1. No Spaces
2. Must have one and only one ‘@’ symbol
3. The ‘@’ symbol cannot be the first character
4. Must have at least one ‘.’ after the ‘@’ symbol
5. Must have at least 1 character between the ‘@’ and the ‘.’
6. The ’.’ cannot be the last character
The code I use is:
=AND( ISERROR(FIND(" ", [YOURCOLUMN],1)), IF(ISERROR(FIND("@", [YOURCOLUMN],2)), FALSE, AND( ISERROR(FIND("@",[YOURCOLUMN], FIND("@", [YOURCOLUMN],2)+1)), IF(ISERROR(FIND(".", [YOURCOLUMN], FIND("@", [YOURCOLUMN],2)+2)), FALSE, FIND(".", [YOURCOLUMN], FIND("@", [YOURCOLUMN],2)+2) < LEN([YOURCOLUMN]) ) ) ) )
I usually also put the rules into the user message box, so that if someone encounters an error because the email address was not formatted correctly, they can refer to the message to work out what is wrong.
25,998 total views, 18 views today