Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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) |