Excel 2007 : Data validation with numbers and text

Hi Madams, welcome to the forum.

Rather than using a convoluted Data Validation formula, perhaps a worksheet change event (macro) would be better suited? This code, which would be added to the code module for the worksheet on which you're entering data, will check column A for any changes as you make them, and determine whether or not the entry is valid. It looks for the three formats you specified above (XX00000, 00000000X and 000000X). You can adjust the code to suit your actual data layout.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("A:A")) Is Nothing Then
    Select Case Len(Target.Value)
        Case Is = 7
            If Asc(UCase(Mid(Target.Value, 1, 1))) < 65 Or Asc(UCase(Mid(Target.Value, 1, 1))) > 90 Then GoTo invalid
            If Asc(UCase(Mid(Target.Value, 2, 1))) < 65 Or Asc(UCase(Mid(Target.Value, 2, 1))) > 90 Then GoTo invalid
            For i = 3 To 7
                If Not IsNumeric(Mid(Target.Value, i, 1)) Then GoTo invalid
            Next i
        Case Is = 9
            For i = 1 To 8
                If Not IsNumeric(Mid(Target.Value, i, 1)) Then GoTo invalid
            Next i
            If Asc(UCase(Mid(Target.Value, 9, 1))) < 65 Or Asc(UCase(Mid(Target.Value, 9, 1))) > 90 Then GoTo invalid
        Case Else
            GoTo invalid
    End Select
End If
Exit Sub

invalid:
MsgBox "Invalid Entry.  Entries must be in the form of XX00000, 00000000X or 000000X. Please try again."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End Sub

This could also be done using Regular Expressions (regex), but I'm not very good at using those just yet.