Selected Answer
You need a macro. This will work:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then
char_count = Len(Target.Value)
first_letter = Left(Target.Value, 1)
second_letter = Mid(Target.Value, 2, 1)
third_letter = Mid(Target.Value, 3, 1)
fourth_letter = Mid(Target.Value, 4, 1)
fifth_letter = Mid(Target.Value, 5, 1)
six_to_nine = Mid(Target.Value, 6, 4)
last_char = Right(Target.Value, 1)
'letter count
If char_count <> 10 And Target.Value <> "" Then
MsgBox "Error"
Target.Value = ""
End If
'first letter value
If UCase(first_letter) <> "A" And UCase(first_letter) <> "B" And UCase(first_letter) <> "C" And UCase(first_letter) <> "D" And Target.Value <> "" Then
MsgBox "Error"
Target.Value = ""
End If
'character value
If IsNumeric(last_char) Or IsNumeric(second_letter) Or IsNumeric(third_letter) Or IsNumeric(fourth_letter) Or IsNumeric(fifth_letter) And Target.Value <> "" Then
MsgBox "Error"
Target.Value = ""
End If
'numbers
If Not (IsNumeric(six_to_nine)) And Target.Value <> "" Then
MsgBox "Error"
Target.Value = ""
End If
'make upper case
Target.Value = UCase(Target.Value)
End If
Application.EnableEvents = True
End Sub
Install this code in the worksheet where you need it to run.
Change A1:A5 to the range of cells on which you want it to run.
Change "Error" to the desired error message.