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

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

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