- **Excel Worksheet Functions**
(*https://www.excelbanter.com/excel-worksheet-functions/*)

- - **alpha numeric data validation for excel**
(*https://www.excelbanter.com/excel-worksheet-functions/211910-alpha-numeric-data-validation-excel.html*)

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(2-LEN(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 |

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. |

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 |

alpha numeric data validation for excelThanks 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: 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? |

alpha numeric data validation for excelOn 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? |

All times are GMT +1. The time now is 03:37 PM. |

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.

ExcelBanter.com