Just to try to get back to blogging again I thought I would post a function that will validate a UK NHS (National Health Service) number in SharePoint. You can use this on any list or library where you need people to enter a valid NHS Number.

NHS Numbers use a check digit to self-validate. This page describes the format and has a tool to check for a valid number

In SharePoint you can specify a validation formula for a column. So in my example I created a column on a list called **NHSNumber **and in screen that defines the column, I set the maximum number of characters to be 10, and in the **Column Validation **section I used the following formula. You can basically just copy and paste this, and substitute your column name for “NHSNumber” in the formula

=IF(LEN(NHSNumber)=10,
IF(NOT(ISERROR(NHSNumber+1)),
OR((11-(MOD((LEFT(NHSNumber,1)*10+
RIGHT(LEFT(NHSNumber,2),1)*9+
RIGHT(LEFT(NHSNumber,3),1)*8+
RIGHT(LEFT(NHSNumber,4),1)*7+
RIGHT(LEFT(NHSNumber,5),1)*6+
RIGHT(LEFT(NHSNumber,6),1)*5+
RIGHT(LEFT(NHSNumber,7),1)*4+
RIGHT(LEFT(NHSNumber,8),1)*3+
RIGHT(LEFT(NHSNumber,9),1)*2),11)))=RIGHT(NHSNumber,1)*1,
AND(
((MOD((LEFT(NHSNumber,1)*10+
RIGHT(LEFT(NHSNumber,2),1)*9+
RIGHT(LEFT(NHSNumber,3),1)*8+
RIGHT(LEFT(NHSNumber,4),1)*7+
RIGHT(LEFT(NHSNumber,5),1)*6+
RIGHT(LEFT(NHSNumber,6),1)*5+
RIGHT(LEFT(NHSNumber,7),1)*4+
RIGHT(LEFT(NHSNumber,8),1)*3+
RIGHT(LEFT(NHSNumber,9),1)*2),11)))=0,
RIGHT(NHSNumber,1)*1=0)
)
)
)

The formula above also checks that the string has 10 characters and that all the characters are digits, before calculating the check digit.

It’s not easy to test validation code, but as a check I used this great post to set up a spreadsheet column to validate a string of text as an NHS Numbers. I checked that my SharePoint formula is giving the same results as the spreadsheet formula, using several hundred randomly-generated 10-digit strings. It all seems to work OK, but if you spot any errors please let me know.

52.453480
-2.157326

### Like this:

Like Loading...

*Related*

Yeah, cool, the algorithm has been on the NHS Data Dictionary website for years…so few people bother to look at it. And too many people are still doing stuff in the presentation layer when it should be in the business layer. Ho hum

Comment by Lars Haggqvist — February 25, 2014 @ 8:44 am