View Single Post
  #24   Report Post  
Old March 16th 16, 03:04 PM posted to microsoft.public.excel.worksheet.functions
[email protected] is offline
external usenet poster
First recorded activity by ExcelBanter: Mar 2016
Posts: 1
Default alpha numeric data validation for excel

Thanks a bunch, very helpful. Can you advise me on how to make changes to the validation rule if I wanted it to end with an 'Alphabet'??

On Friday, November 28, 2008 at 2:17:19 PM UTC-5, T. Valko wrote:
That allows more than 7 characters:


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

Microsoft Excel MVP

"vezerid" wrote in message
With the validated cell being A2:


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

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

Any ideas?