We have a SharePoint List which has validation on it to check for a National Insurance number (9 characters long, 3 letters and 6 numbers).

However, it's not a mandatory field, so we would also like to accept the form if the field is left totally blank.

Is it possible to accept either scenario:

  • a blank field
  • or 9 characters

In the list valuation formula you could do:

=OR([Field]="", LEN([Field])=9)

This would allow the field to be blank or have 9 characters.


Modifying the formula from your link would look like:

=OR([New NI No.]="", (LEN([New NI No.])=9)
+(IF(OR((AND(CODE(MID([New NI No.],1,1))>64,
CODE(MID([New NI No.],1,1))<91)),
(AND(CODE(MID([New NI No.],1,1))>96,
CODE(MID([New NI No.],1,1))<123))),1,0))
+(IF(OR((AND(CODE(MID([New NI No.],2,1))>64,
CODE(MID([New NI No.],2,1))<91)),
(AND(CODE(MID([New NI No.],2,1))>96,
CODE(MID([New NI No.],2,1))<123))),1,0))
+(CODE(MID([New NI No.],3,1))>47)
+(CODE(MID([New NI No.],3,1))<58)
+(CODE(MID([New NI No.],4,1))>47)
+(CODE(MID([New NI No.],4,1))<58)
+(CODE(MID([New NI No.],5,1))>47)
+(CODE(MID([New NI No.],5,1))<58)
+(CODE(MID([New NI No.],6,1))>47)
+(CODE(MID([New NI No.],6,1))<58)
+(CODE(MID([New NI No.],7,1))>47)
+(CODE(MID([New NI No.],7,1))<58)
+(CODE(MID([New NI No.],8,1))>47)
+(CODE(MID([New NI No.],8,1))<58)
+(IF(OR((AND(CODE(MID([New NI No.],9,1))>64,
CODE(MID([New NI No.],9,1))<91)),
(AND(CODE(MID([New NI No.],9,1))>96,
CODE(MID([New NI No.],9,1))<123))),1,0))
  • thanks, I tried this (whilst it does look exactly like what I need), it didn't work with my code. – Mark Nov 15 '13 at 15:46
  • there isn't room to post entire validation which I have used, but there is a link to it here: social.technet.microsoft.com/Forums/sharepoint/en-US/… – Mark Nov 15 '13 at 15:51
  • Modified the formula from the post and updated the answer. – John Chapman Nov 15 '13 at 16:13
  • I've just tried that (and also copied and pasted from above, in case I made a mistake), but it still returns a validation error when leaving the field blank, which says: "The validation formula has evaluated to an error. " – Mark Nov 15 '13 at 17:11
  • I think I missed a parens. Add ) to the end. – John Chapman Nov 15 '13 at 17:30

solved it.

I went back to basics, started with validation for 1 character: either 1 or a or A or blank - got this working, then built it up around that. it highlighted the simple problem all along - it was the =18 at the end!

it should have been =15 at the end.

I couldn't understand why four lines of code [for the letters] counted for '1' and two lines of code for [the numbers] counted as '2'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.