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
