Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Here is an even shorter formula that also seems to work...
=AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),SUMPRODUC T(--ISNUMBER(--MID(A1,ROW($1:$99),1)))=5) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Cancel that... this formula lets in unacceptable entries.
-- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Here is an even shorter formula that also seems to work... =AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),SUMPRODUC T(--ISNUMBER(--MID(A1,ROW($1:$99),1)))=5) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Try entering this:
' 12345 That is: apostrophe<space<space12345 -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Here is an even shorter formula that also seems to work... =AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),SUMPRODUC T(--ISNUMBER(--MID(A1,ROW($1:$99),1)))=5) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Your message was sent before you saw my second posting acknowledging the
formula didn't work... it lets lots of things through that the OP wouldn't want. -- Rick (MVP - Excel) "T. Valko" wrote in message ... Try entering this: ' 12345 That is: apostrophe<space<space12345 -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Here is an even shorter formula that also seems to work... =AND(EXACT(LEFT(A1,2),UPPER(LEFT(A1,2))),SUMPRODUC T(--ISNUMBER(--MID(A1,ROW($1:$99),1)))=5) -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Okay, this is only a little shorter, but it does get rid of the volatile
function calls (I wonder if that matter in a validation formula)... =SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7 -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
=SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7
it does get rid of the volatile function calls (I wonder if that matter in a validation formula) Well, it leaves the formula vulnerable to row insertions and it accepts more than 7 characters. It appears that a formula with a volatile function used as a validation rule doesn't "act" volatile. In a test file I entered the validation rule using INDIRECT. Closed the file, opened the file, didn't do anything at all, then closed the file without Excel asking if I wanted to save changes ( a telltale sign that a volatile function is in use) We can shorten it by a few more keystrokes while at the same time adding a length test since a formula entered in a refedit is automatically processed as an array: =SUM(--(LEN(A2)=7),--(ABS(CODE(MID(A2,ROW($1:$2),1))-77.5)<=12.5),COUNT(-MID(A2,ROW($3:$7),1)))=8 So, as long as you don't insert new rows (in certain places) that looks pretty good. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Okay, this is only a little shorter, but it does get rid of the volatile function calls (I wonder if that matter in a validation formula)... =SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7 -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Doh... of course it needed a LEN test (can't believe I missed that).
So then, I guess INDIRECT is indirect only when used directly.<g Yes, using the automatic array process is definitely better. Of course, I tested my logic out on the grid directly and used SUMPRODUCT during testing to avoid hitting the Ctrl+Shift+Enter (just lazy on my part); then, because my wife wanted to use the computer, I rushed posting my findings without even thinking about the array processing part of it. Thanks for catching all the issue I missed. -- Rick (MVP - Excel) "T. Valko" wrote in message ... =SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7 it does get rid of the volatile function calls (I wonder if that matter in a validation formula) Well, it leaves the formula vulnerable to row insertions and it accepts more than 7 characters. It appears that a formula with a volatile function used as a validation rule doesn't "act" volatile. In a test file I entered the validation rule using INDIRECT. Closed the file, opened the file, didn't do anything at all, then closed the file without Excel asking if I wanted to save changes ( a telltale sign that a volatile function is in use) We can shorten it by a few more keystrokes while at the same time adding a length test since a formula entered in a refedit is automatically processed as an array: =SUM(--(LEN(A2)=7),--(ABS(CODE(MID(A2,ROW($1:$2),1))-77.5)<=12.5),COUNT(-MID(A2,ROW($3:$7),1)))=8 So, as long as you don't insert new rows (in certain places) that looks pretty good. -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Okay, this is only a little shorter, but it does get rid of the volatile function calls (I wonder if that matter in a validation formula)... =SUMPRODUCT(--(ABS(CODE(MID(A20,ROW($1:$2),1))-77.5)<=12.5))+SUMPRODUCT(--ISNUMBER(--MID(A20,ROW($3:$99),1)))=7 -- Rick (MVP - Excel) "T. Valko" wrote in message ... 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? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Hi,
Try this formula. While in cell B85, enter this in Data Validation Custom. =AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5))) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... 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? |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
That formula fails when the number part has a decimal point or an E (or e)
in it. For example, try these values... AB123.5 AB123e5 -- Rick (MVP - Excel) "Ashish Mathur" wrote in message ... Hi, Try this formula. While in cell B85, enter this in Data Validation Custom. =AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5))) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... 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? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
"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) |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Hi,
Thank you for your comments. In the original question, it has been mentioned that the last 5 characters are numbers - therefore the possibility of having a . or e is ruled out. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Rick Rothstein" wrote in message ... That formula fails when the number part has a decimal point or an E (or e) in it. For example, try these values... AB123.5 AB123e5 -- Rick (MVP - Excel) "Ashish Mathur" wrote in message ... Hi, Try this formula. While in cell B85, enter this in Data Validation Custom. =AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5))) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... 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? |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
The OP was after a Data/Validation formula that would guarantee a user's
input was of the form letter-letter-number-number-number-number-number... that is the desired entry format... that doesn't mean the user will *always* type in such an entry... for example, the user could type in AB123e5 by mistake (accidentally hitting the 'e' when he/she went for the '4' instead) or he/she could type in AB123.4 just to be mischievous... I would think the Data/Validation formula should be able to handle such occurrences. -- Rick (MVP - Excel) "Ashish Mathur" wrote in message ... Hi, Thank you for your comments. In the original question, it has been mentioned that the last 5 characters are numbers - therefore the possibility of having a . or e is ruled out. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Rick Rothstein" wrote in message ... That formula fails when the number part has a decimal point or an E (or e) in it. For example, try these values... AB123.5 AB123e5 -- Rick (MVP - Excel) "Ashish Mathur" wrote in message ... Hi, Try this formula. While in cell B85, enter this in Data Validation Custom. =AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5))) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... 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? |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
"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. About the volatile INDIRECT... "T. Valko" wrote... It appears that a formula with a volatile function used as a validation rule doesn't "act" volatile. In a test file I entered the validation rule using INDIRECT. Closed the file, opened the file, didn't do anything at all, then closed the file without Excel asking if I wanted to save changes ( a telltale sign that a volatile function is in use) Any thoughts on that? -- Biff Microsoft Excel MVP |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
Hi,
Agreed. Thank you for the clarification. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Rick Rothstein" wrote in message ... The OP was after a Data/Validation formula that would guarantee a user's input was of the form letter-letter-number-number-number-number-number... that is the desired entry format... that doesn't mean the user will *always* type in such an entry... for example, the user could type in AB123e5 by mistake (accidentally hitting the 'e' when he/she went for the '4' instead) or he/she could type in AB123.4 just to be mischievous... I would think the Data/Validation formula should be able to handle such occurrences. -- Rick (MVP - Excel) "Ashish Mathur" wrote in message ... Hi, Thank you for your comments. In the original question, it has been mentioned that the last 5 characters are numbers - therefore the possibility of having a . or e is ruled out. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Rick Rothstein" wrote in message ... That formula fails when the number part has a decimal point or an E (or e) in it. For example, try these values... AB123.5 AB123e5 -- Rick (MVP - Excel) "Ashish Mathur" wrote in message ... Hi, Try this formula. While in cell B85, enter this in Data Validation Custom. =AND(LEN(B85)=7,CODE(LEFT(B85,1))=65,CODE(LEFT(B8 5,1))<=90,CODE(MID(B85,2,1))=65,CODE(MID(B85,2,1) )<=90,ISNUMBER(1*RIGHT(B85,5))) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... 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? |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
alpha numeric data validation for excel
"T. Valko" wrote...
.... About the volatile INDIRECT... "T. Valko" wrote... It appears that a formula with a volatile function used as a validation rule doesn't "act" volatile. In a test file I entered the validation rule using INDIRECT. Closed the file, opened the file, didn't do anything at all, then closed the file without Excel asking if I wanted to save changes ( a telltale sign that a volatile function is in use) Any thoughts on that? Validation rules only apply when you enter something into cells with validation rules. If you're not entering anything, e.g., when saving files, then the validation rule wouldn't be evaluated. Key point: validation rules are OUTSIDE the calculation dependency tree. Validation permits initial entry, which in turn triggers minimal recalc, which also recalcs all formulas that call volatile functions, then evaluates the validation rule and takes appropriate action. In that context, custom validation formulas are ALWAYS volatile no matter what functions they call since validation rules are ALWAYS evaluated upon entry. Also, FWIW, if you select multiple cells, if the active cell doesn't contain a validation rule but other selectedd cells do contain validation rules, you could type anything you want in the active cell and press [Ctrl]+[Enter], and Excel will happily enter the active cell's value into all the cells without triggering validation in the other selected cells. IOW, validation rules are only evaluated for the active cell at the time of entry. And, as commonly known, validation isn't triggered by pasting into cells with validation rules. If the OP's users could be pasting values in from other programs (e.g., copying from PDF files), no validation rule will help. Only event handlers and validation formulas in other cells provide relatively robust validation. Excel's own Data Validation feature is an unreliable toy, as industrial strength (NOT!) as internal passwords. |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#22
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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: 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? |
#25
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 alpha-numeric data? | Excel Discussion (Misc queries) | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) |