Excel Formula to Verify If National Insurance Numbers (NINOs) Are Valid
If you have a large data set of National Insurance Numbers (NINOs) and want to check if they are in the correct format, you can use the formula below.
The criteria for a valid National Insurance Number are the following :
It must be 9 characters long and start with two letters, followed by six numbers and one letter e.g. AB123456C.
The first and second characters cannot be the letters D, F, I, Q, U, and V.
The second character cannot be the letter O.
The first two characters cannot be any of the following letter combinations: BG, GB, KN, NK, NT, TN and ZZ.
The final character must be either A, B, C or D.
This formula checks whether the National Insurance Numbers provided are in the correct format
We recommend using Excel’s paste special option to paste the formula as text or Unicode text.
=IF(AND(LEN(A2)=9,OR(LEFT(A2,1)={"a";"b";"c";"e";"g";"h";"j";"k";"l";"m";"n";"o";"p";"r";"s";"t";"w";"x";"y";"z"}),OR(MID(A2,2,1)={"a";"b";"c";"e";"g";"h";"j";"k";"l";"m";"n";"p";"r";"s";"t";"w";"x";"y";"z"}),NOT(OR(LEFT(A2,2)={"BG";"GB";"KN";"NK";"NT";"TN";"ZZ"})),ISNUMBER(--MID(A2,3,6)),OR(RIGHT(A2,1)={"a","b","c","d"})),"Valid NI Number","Not a valid NI Number")
The above formula returns “Valid NI Number” if the National Insurance Number provided meets the criteria we listed above. If it does not meet the criteria we listed above the formula returns “Not a valid NI Number”.
A breakdown of the National Insurance Validation formula
LEN(A2)=9
This part of the formula checks if the NINO provided is 9 characters long.
OR(LEFT(A2,1)={"a";"b";"c";"e";"g";"h";"j";"k";"l";"m";"n";"o";"p";"r";"s";"t";"w";"x";"y";"z"})
This part of the formula checks if the first character of the NINO is one of the letters inside the brackets.
OR(MID(A2,2,1)={"a";"b";"c";"e";"g";"h";"j";"k";"l";"m";"n";"p";"r";"s";"t";"w";"x";"y";"z"})
This part of the formula checks if the second character of the NINO is one of the letters inside the brackets.
NOT(OR(LEFT(A2,2)={"BG";"GB";"KN";"NK";"NT";"TN";"ZZ"}))
The part of the formula checks to see if the first two characters are not any of the letter combinations inside the brackets.
ISNUMBER(--MID(A2,3,6))
This part of the formula checks to see if characters 3 to 8 of the NINO provided are numbers.
OR(RIGHT(A2,1)={"a","b","c","d"}))
This part of the formula checks to see if the final character of the NINO provided is A, B, C or D.
References
1. National Insurance Manual by HM Revenue & Customs
https://www.gov.uk/hmrc-internal-manuals/national-insurance-manual/nim39110