Hi Sar

Use Data> Validation>Custom > and array entered formula of

{=AND(SUM(CODE(MID(A1,ROW(INDIRECT("1:"&3)),1)))>194,ISNUMBER(--(RIGHT(A1,4))))}

To Enter or Edit an Array formula, use Control Shift Enter (CSE) not
just Enter.
Do not type the curly braces  {   }  yourself. When you use CSE, Excel
will enter these for you.

On the Error Alert tab type a message for the use showing the type of
entry you expect.

Assuming that @@@ means the first 3 characters are either upper or lower
case **letters** from the alphabet only, does this Worksheet Macro do what
you want?

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column <> 1 Or Target.Value = "" Then Exit Sub
   If Not Target.Value Like "[A-Za-z][A-Za-z][A-Za-z]####" Then
       MsgBox "Wrong Format!"
       Target.Value = ""
       Target.Activate
   End If
End Sub

where you would change the number 1 in 'Target.Column <> 1' to the column
number you want to filter (and, of course, change the MessageBox message
also).

Rick

Use the following *array* formula in Data Validation (Custom)

=PRODUCT((CODE(MID(A1,ROW(1:3),1))>64)*(CODE(MID(A1,ROW(1:3),
1))<=91))*ISNUMBER(--RIGHT(A1,4))*(LEN(A1)=7)

Use Shift+Ctrl+Enter instead of just clicking OK.

HTH
Kostis Vezerides

Sar* <S...@discussions.microsoft.com> wrote...

...

The most general approach is something like

=AND(COUNT(SEARCH(MID(X99,ROW(1:3),1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),
SEARCH(MID(X99,ROW(4:7),1),"0123456789"))=7,LEN(X99)=7)

Two of the other suggestions checked that the 4 through 7 characters
were numeric using --ISNUMBER(A1,4). First, the numeric conversion
requires only one unary minus, and second, this would blissfully
accept such strings as

1E12
-300
28.5
-2.5
33.%
7/12

which is probably not what you want. There are times when simple beats
clever.

Another possibility, with special note to Harlan's remark in his reply to my
other post in this sub-thread, namely,

   "Event handlers only work when macros are enabled,
    and it's simple for users to disable macros"

is to create your own format checker via a macro. Here is a function macro
that should be usable which will expose VBA's Like operator for use in a
worksheet. Right click the tab at the bottom of the sheet, select "View
code",  add a Module to the worksheet and put this code in it...

   Public Function IsLike(R As Range, Pattern As String) As Boolean
     Dim C As Range
     IsLike = True
     For Each C In R
       If Not C.Value Like Pattern Then
          IsLike = False
          Exit Function
       End If
     Next
   End Function

For your stated need, just put the following into a helper column starting
at the first cell you want to check and then copy down...

   =IsLike(A1,"[A-Z][A-Z][A-Z]####")

Of course, replace the A1 with the actual cell reference you want to check.

Rick