Tracy van der Schyff

 #Office365Challenge Do you need to validate Identity Numbers? Well, here you go.

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
• Day 289 – SharePoint Column Validations on Telephone Numbers

Yesterday we validated telephone numbers, today we’ll look at SA Identity Numbers. Keep in mind this does NOT check validity of an ID number (here’s a cool blog about that), it only checks valid characters and length.

In my example, I would like to validate the length of the ID number as well as that it only contains numbers. This is the formula I’m using: 

=IF(LEN([Identity Number])=13,ISNUMBER([Identity Number]+0),FALSE)

or you can use:

=AND(LEN([Identity Number])=13,ISNUMBER([Identity Number]+0))

1. Check the length is 13 characters
2. Check that they are all numbers

Today’s post ended up being quite a challenge for me, between all the AND’s OR’s, IFERRORS etc. I really struggled to find formula that actually worked. Thanks to the comments on this Technet post, I eventually came right. Thanks guys!

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 above and replace [Identity Number] with your column name).

This is what the Form and Error Message will look like. If the data does not only contain numbers and is not 13 characters long, you will receive the message:

Have fun!

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.