Home 
Search 
Today's Posts 
#21




alpha numeric data validation for excel
 Biff Microsoft Excel MVP "T. Valko" wrote in message ... "Harlan Grove" wrote in message ... "T. Valko" wrote... ... This seems to work: =SUM(2LEN(SUBSTITUTE(LEFT(A2,2),CHAR(ROW(INDIRECT("65:90 "))),"")), (COUNT(MID(A2,ROW(INDIRECT("3:7")),1))=5),(LEN(A2)=7))=4 ... Or avoiding the volatile INDIRECT function but taking advantage of ASCII encoding, =AND(LEN(x)=7,ABS(CODE(MID(x,{1;2},1))77.5)<13,COUNT(MID(x, {3;4;5;6;7},1))=5) Nice one, Harlan. I should've realized we could use array constants rather than ROW(INDIRECT(...)) since it's only a few characters. Ooops! I can't believe that between us we didn't catch this... Can't use array constants in a DataValidationCustom rule.  Biff Microsoft Excel MVP 
#22




alpha numeric data validation for excel
"T. Valko" wrote...
.... Ooops! I can't believe that between us we didn't catch this... Can't use array constants in a DataValidationCustom rule. OK, should have causght that. =AND(ABS(CODE(A1)77.5)<13,ABS(CODE(MID(A1,2,1))77.5)<13, COUNT(1/(MID(A1,3,6)=TEXT(MID(A1,3,5),"00000")))) Note that the 6 as 3rd arg in the 1st MID call is intentional. It eliminates the need for a LEN test. 
#23




alpha numeric data validation for excel
"Harlan Grove" wrote in message ... "T. Valko" wrote... ... Ooops! I can't believe that between us we didn't catch this... Can't use array constants in a DataValidationCustom rule. OK, should have causght that. =AND(ABS(CODE(A1)77.5)<13,ABS(CODE(MID(A1,2,1))77.5)<13, COUNT(1/(MID(A1,3,6)=TEXT(MID(A1,3,5),"00000")))) Note that the 6 as 3rd arg in the 1st MID call is intentional. It eliminates the need for a LEN test. COUNT(1/(MID(A1,3,6)=TEXT(MID(A1,3,5),"00000"))) This seems to work just as well: MID(A1,3,6)=TEXT(MID(A1,3,5),"00000")  Biff Microsoft Excel MVP 
#24




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 UTC5, T. Valko wrote: That allows more than 7 characters: AB12345xxxxxx AB1234567890 This seems to work: =SUM(2LEN(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? 
#25




alpha numeric data validation for excel
On Saturday, 29 November 2008 04:23:44 UTC+11, 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? On Saturday, 29 November 2008 04:23:44 UTC+11, 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? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Sorting alpha numeric data  Excel Discussion (Misc queries)  
A validation rule on Alpha and Numeric characters  Excel Worksheet Functions  
How do I group alpha numeric data in excel?  Excel Discussion (Misc queries)  
can i write a macro to truncate alphanumeric data?  Excel Discussion (Misc queries)  
The colums changed from alpha to numeric how do you make it alpha  Excel Discussion (Misc queries) 