Tracy van der Schyff

 #Office365Challenge This post will show you how to validate length and use of special characters in a telephone number.

Related Posts:

• Day 285 – SharePoint List Validations – Force field completion (based on value of another field)
• Day 286 – SharePoint List Validations – Force completion of one of two columns
• Day 287 – SharePoint List Validations – Minimum Length of Characters

In the previous posts we used List Validations (under the List Settings). This post uses Column Validations which you set on the actual Column. List Validations are great when using multiple columns in the formula, Columns validations work great if only that specific column is “involved” in the formula.

In my example, I would like to validate how telephone numbers are populated. It will check for the following:

1. Starts with “+”
2. Character 4 is a space
3. Character 7 is a space
4. Character 11 is a “-“
5. Add all numbers and multiply by 1 to determine if they are numbers (will give error if it starts with zero or includes text)

Let’s just manage some expectations right upfront. This only works if you add a country code in front with a “+” and ONLY if that prefix is only 2 characters long, for example +27 which is SA. Anything more than 2 characters will give an error. Here is a great blog by The Chris Kent which doesn’t use country codes, BUT it gives me errors because it validates the number by multiplying it by 1 – which doesn’t work with SA numbers as most of our numbers start with “0” – which then returns an error. So… I’ll have to figure that one out some other day – comments on how to overcome this will be very welcome!

How to add Column Validations:

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

I’ve added a default value to guide the user, added the formula and added the custom user message if it cannot validate the data:

(Formula I used can be copied from text below images)

This is what the Form and Error Message will look like. If you mess around with the length, special characters etc. it will give an error. Keep in mind – it will also give an error if you number starts with +0 (I’ll have to figure this out some other day, for SA this will work perfectly):

When I build the formulas, I put my text into a table and number the characters. This helps me not to make mistakes and easily count characters:

This is the formula I used, remember to replace [Phone] with your column name:

=AND(

LEN([Phone])=15,

IF(ISERROR(FIND(“+”, [Phone],1)),

FALSE,

(FIND(“+”, [Phone]) = 1)

),

IF(ISERROR(FIND(” “, [Phone],4)),

FALSE,

(FIND(” “, [Phone], 4) = 4)

),

IF(ISERROR(FIND(” “, [Phone],7)),

FALSE,

(FIND(” “, [Phone], 7) = 7)

),

IF(ISERROR(FIND(“-“, [Phone],11)),

FALSE,

(FIND(“-“, [Phone], 11) = 11)

),

IF(ISERROR(1*CONCATENATE(MID([Phone], 2, 2), MID([Phone], 5, 2), MID([Phone], 8, 3) , MID([Phone], 12, 4))),

FALSE,

AND(

1*CONCATENATE(MID([Phone], 2, 2), MID([Phone], 5, 2), MID([Phone], 8, 3) , MID([Phone], 12, 4)) > 1000000000

)

)

)

Enjoy!! And remember – if you have comments on how to overcome my “start with zero” problem or the length of the Country Code – I would appreciate it.

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.