ATOI365D #Office365Challenge Do you need to validate Email Addresses? This blog will help.

Day: 291 of 365, 74 left
Tools: Office 365, SharePoint
Description: SharePoint Column Validations on Email Addresses
Audience: All

Related Posts:


I’m on a roll with validations you can do on lists and columns. Today’s post will help with validations on email addresses. Thanks to this blog for making it easy

In my example, I would like to validate the use of special characters in specific places. This is what I would like to accomplish:

  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

Note:  When getting formulas from blogs – always check the use of “;” vs “,” as your regional settings has an effect on this.

How to add Column Validations:

When adding the column, you’ll see a section called Column Validation which can be expanded.

I’ve added the formula and the custom user message if it cannot validate the data:

(Copy formula from below and replace [Email Address] with your column name).

161216001

This is what the Form and Error Message will look like. If the data does not comply, you will receive the message:

161216002

Here is the formula you’ll use:

=AND(

ISERROR(FIND(” “, [EMAIL ADDRESS],1)),

IF(ISERROR(FIND(“@”, [EMAIL ADDRESS],2)),

FALSE,

AND(

ISERROR(FIND(“@”,[EMAIL ADDRESS], FIND(“@”, [EMAIL ADDRESS],2)+1)),

IF(ISERROR(FIND(“.”, [EMAIL ADDRESS], FIND(“@”, [EMAIL ADDRESS],2)+2)),

FALSE,

FIND(“.”, [EMAIL ADDRESS], FIND(“@”, [EMAIL ADDRESS],2)+2) < LEN([EMAIL ADDRESS])

)

)

)

)

Sorted. See you tomorrow.

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.
Keep in mind that these tips and tricks do not only apply to Office 365 – but where applicable, to the overall Microsoft Office Suite and SharePoint.

Advertisements