Try this UDF either in a cell or in a custom validation formula:
Function Val_AN(ANstring)
   Val_AN = True
   If Len(ANstring) <> 10 Then
       Val_AN = False
   Else
       For d = 1 To 10
           If d <= 5 Or d = 10 Then
               If Mid(ANstring, d, 1) < "A" Or Mid(ANstring, d, 1) > "Z" Then
                   Val_AN = False
                   Exit For
               End If
           Else
               If Mid(ANstring, d, 1) < "0" Or Mid(ANstring, d, 1) > "9" Then
                   Val_AN = False
                   Exit For
               End If
           End If
       Next d
   End If
End Function

Regards,
Stefi

„Montu” ezt írta:

=IF((LEN(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))-1))=5)*(ISNUMBER(SUM(MID(A1,ROW(INDIRECT("6:8")),1)+0)))*(LEN(A1)=10)*(ISERR(RIGHT(A1,1)+0)),"OK","Not OK")

ctrl+shift+enter, not just enter

If you want this as a data validation rule copy/paste the formula into the
data validation refedit. If you want this as a worksheet function enter the
formula as an array ** :

=SUM(5-LEN(SUBSTITUTE(UPPER(LEFT(A1,5)),CHAR(ROW(INDIRECT("65:90"))),"")),COUNT(--MID(A1,6,4)),COUNT(SEARCH(CHAR(ROW(INDIRECT("65:90"))),RIGHT(A1))))=7

Meets these conditions:

Length must be 10 chars long
The first 5 chars must be A-Z or a-z only
The 6th to 9th chars must be the digits 0-9 only
The 10th char must be A-Z or a-z only

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)