View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
T. Valko T. Valko is offline
external usenet poster
 
Posts: 15,768
Default alpha numeric data validation for excel

Glad to help.

I'm always looking for new ideas myself!

--
Biff
Microsoft Excel MVP


"vezerid" wrote in message
...
Biff,

thanks for the new ideas you gave me.

Kostis

On Nov 28, 9:17 pm, "T. Valko" wrote:
That allows more than 7 characters:

AB12345xxxxxx
AB1234567890

This seems to work:

=SUM(2-LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")),--(COUNT(-MID(A2,ROW(INDIRECT("3:7")),1))=5),--(LEN(A2)=7))=4

--
Biff
Microsoft Excel MVP

"vezerid" wrote in message

...
With the validated cell being A2:

=PRODUCT(--(CODE(MID(A2,ROW(INDIRECT("1:2")),1))=65))*PRODUC T(--(CODE
(MID(A2,ROW(INDIRECT("1:2")),1))<=90))*PRODUCT(--ISNUMBER(--MID(A2,ROW
(INDIRECT("3:7")),1)))

A bit complex but ensures that no . or E will be used in the last five
digits.

HTH
Kostis Vezerides

On Nov 28, 7:23 pm, wrote:

I want to use a non vb formula in data validation for an excel formula
to make the user enter the first 2 characters of a field as UPPERCASE
letters i.e. AB12345 - the format is always the same 2 letters 5
numbers.


Any ideas?