| Home |
| Search |
| Today's Posts |
|
#1
November 28th 08, 05:23 PM
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
November 28th 08, 06:07 PM
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
November 28th 08, 07:17 PM
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
November 28th 08, 07:38 PM
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
November 28th 08, 07:40 PM
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
November 28th 08, 07:49 PM
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
November 28th 08, 07:51 PM
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
November 28th 08, 07:57 PM
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
November 28th 08, 08:09 PM
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
November 28th 08, 08:40 PM
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? |
| 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) | |||
Linear Mode
