ExcelBanter - Check for valid national insurance number

Try this, based on your example

Public Function IsNatInsurNum(DataInput As Variant) As Boolean
Dim mydata As String, Temp As Long
IsNatInsurNum = False
mydata = CStr(DataInput) 'convert to string
If Len(mydata) = 9 Then 'nine characters
If (Asc(Left(mydata, 1)) = 65 And Asc(Left(mydata, 1)) <= 90)
Then 'first char is A to Z
If (Asc(Mid(mydata, 2, 1)) = 65 And Asc(Mid(mydata, 2, 1)) <=
90) Then 'second char is A to Z
On Error Resume Next
Err.Clear
Temp = CDbl(Mid(mydata, 3, 6)) 'check 6 digits in middle
If Temp < 0 Then
If (Asc(Right(mydata, 1)) = 65 And
Asc(Right(mydata, 1)) <= 90) Then 'last char is A to Z
IsNatInsurNum = True
End If
End If
On Error GoTo 0
End If
End If
End If

End Function

regards
Paul
On May 2, 10:58 am, Keith74 wrote:
Hi there

Does anyone have some code to check that the value in a variable is a
valid national insurance number e.g. AB123456C ?

Cheers

Keith