I didn't technically create this, just saw a regex for it in the DOM Explorer when using a form on the HMRC website, so chucked it into a function as it's something I've seen asked for a few times - tweaked a little to not allow things such as "AB123456C" so the format should be any of the allowed prefixes, followed by 6 numbers and a letter from A-D
Code
Function IsValidNINO(NINO As String) As Boolean
With CreateObject("VBScript.RegExp")
.Pattern = "^(AA|AB|AE|AH|AK|AL|AM|AP|AR|AS|AT|AW|AX|AY|AZ|BA|BB|BE|BH|BK|BL|" & _
"BM|BT|CA|CB|CE|CH|CK|CL|CR|EA|EB|EE|EH|EK|EL|EM|EP|ER|ES|ET|EW|EX|" & _
"EY|EZ|GY|HA|HB|HE|HH|HK|HL|HM|HP|HR|HS|HT|HW|HX|HY|HZ|JA|JB|JC|JE|" & _
"JG|JH|JJ|JK|JL|JM|JN|JP|JR|JS|JT|JW|JX|JY|JZ|KA|KB|KE|KH|KK|KL|KM|" & _
"KP|KR|KS|KT|KW|KX|KY|KZ|LA|LB|LE|LH|LK|LL|LM|LP|LR|LS|LT|LW|LX|LY|" & _
"LZ|MA|MW|MX|NA|NB|NE|NH|NL|NM|NP|NR|NS|NW|NX|NY|NZ|OA|OB|OE|OH|OK|" & _
"OL|OM|OP|OR|OS|OX|PA|PB|PC|PE|PG|PH|PJ|PK|PL|PM|PN|PP|PR|PS|PT|PW|" & _
"PX|PY|RA|RB|RE|RH|RK|RM|RP|RR|RS|RT|RW|RX|RY|RZ|SA|SB|SC|SE|SG|SH|" & _
"SJ|SK|SL|SM|SN|SP|SR|SS|ST|SW|SX|SY|SZ|TA|TB|TE|TH|TK|TL|TM|TP|TR|" & _
"TS|TT|TW|TX|TY|TZ|WA|WB|WE|WK|WL|WM|WP|YA|YB|YE|YH|YK|YL|YM|YP|YR|" & _
"YS|YT|YW|YX|YY|YZ|ZA|ZB|ZE|ZH|ZK|ZL|ZM|ZP|ZR|ZS|ZT|ZW|ZX|ZY)[0-9]" & _
"{6}[A-D]$"
IsValidNINO = .test(UCase(NINO)) And Not UCase(NINO) = "AB123456C"
End With
End Function
Display More
Either use in VBA:
Code
Dim valid As Boolean, testNINO As String
testNINO = "AB123456C"
valid = IsValidNINO(testNINO)
MsgBox testNINO & " is " & IIf(valid, vbNullString, "not ") & "a valid NINO."
Or as a worksheet function:
=IsValidNINO(A1)