Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation and Input Mask
Using Excel 2003's Data Validation feature (rather than VBA code), is it
possible to create an input mask? Specifically, I need to test for certain valid characters in a Project ID as follows: Example: PA264.04 Position 1: can be either a "B" or "P" Positions 2-5: can be either uppercase letters or numbers Position 6: must be a decimal point Positions 7-8: can only be numeric (00 - 99) Any help would be greatly appreciated. Thanks. Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation and Input Mask
Bob,
For example, if your Project ID cell is in cell A2, select cell A2 and use Data Validation / "Allow" Custom, and use the formula =AND(OR(LEFT(A2,1)="P",LEFT(A2,1)="B"),EXACT(MID(A 2,2,4),UPPER(MID(A2,2,4))),MID(A2,6,1)=".",NOT(ISE RROR(VALUE(RIGHT(A2,2))))) HTH, Bernie MS Excel MVP "Bob" wrote in message ... Using Excel 2003's Data Validation feature (rather than VBA code), is it possible to create an input mask? Specifically, I need to test for certain valid characters in a Project ID as follows: Example: PA264.04 Position 1: can be either a "B" or "P" Positions 2-5: can be either uppercase letters or numbers Position 6: must be a decimal point Positions 7-8: can only be numeric (00 - 99) Any help would be greatly appreciated. Thanks. Bob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Validation and Input Mask
Bernie,
The formula works perfectly! Thanks! Bob "Bernie Deitrick" wrote: Bob, For example, if your Project ID cell is in cell A2, select cell A2 and use Data Validation / "Allow" Custom, and use the formula =AND(OR(LEFT(A2,1)="P",LEFT(A2,1)="B"),EXACT(MID(A 2,2,4),UPPER(MID(A2,2,4))),MID(A2,6,1)=".",NOT(ISE RROR(VALUE(RIGHT(A2,2))))) HTH, Bernie MS Excel MVP "Bob" wrote in message ... Using Excel 2003's Data Validation feature (rather than VBA code), is it possible to create an input mask? Specifically, I need to test for certain valid characters in a Project ID as follows: Example: PA264.04 Position 1: can be either a "B" or "P" Positions 2-5: can be either uppercase letters or numbers Position 6: must be a decimal point Positions 7-8: can only be numeric (00 - 99) Any help would be greatly appreciated. Thanks. Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation-Input Mask | Excel Discussion (Misc queries) | |||
Input Mask for Dates | Excel Discussion (Misc queries) | |||
Input Mask | Excel Worksheet Functions | |||
input mask in excel | Excel Worksheet Functions | |||
Input Mask | New Users to Excel |