input masks and validation
 

when entering data in a cell, i need to restrict users to enter the following
alphanumeric combination and nothing more.

"aa####a", where "a" is an alphabetic character (a-z) and "#" is a number
(0-9)

i cannot figure out the formula to use under the 'custom' section of Data -
Validation

pls help! thanks

You might try this validation formula, where the validation cell is A1:

=AND(LEN(A1)=7,LEFT(A1,2)="aa",RIGHT(A1,1)="a",VAL UE(MID(A1,3,4)))

--
Jim
"archiboy" wrote in message
...
| when entering data in a cell, i need to restrict users to enter the
following
| alphanumeric combination and nothing more.
|
| "aa####a", where "a" is an alphabetic character (a-z) and "#" is a number
| (0-9)
|
| i cannot figure out the formula to use under the 'custom' section of
Data -
| Validation
|
| pls help! thanks

Thanks Jim. It works fine.
.....considering that "aa" limits the user to enter just the letters 'aa',
what should be the input mask should I need to allow any alphabetic
character, that is, a-z?

cheers

"Jim Rech" wrote:

You might try this validation formula, where the validation cell is A1:

=AND(LEN(A1)=7,LEFT(A1,2)="aa",RIGHT(A1,1)="a",VAL UE(MID(A1,3,4)))

--
Jim
"archiboy" wrote in message
...
| when entering data in a cell, i need to restrict users to enter the
following
| alphanumeric combination and nothing more.
|
| "aa####a", where "a" is an alphabetic character (a-z) and "#" is a number
| (0-9)
|
| i cannot figure out the formula to use under the 'custom' section of
Data -
| Validation
|
| pls help! thanks

This may do it:

=AND(LEN(A1)=7,CODE(LEFT(UPPER(A1),1))64,CODE(LEF T(UPPER(A1),1))<91,CODE(MID(UPPER(A1),2,1))64,COD E(MID(UPPER(A1),2,1))<91,RIGHT(A1,1)="a",VALUE(MID (A1,3,4)))

--
Jim
"archiboy" wrote in message
...
| Thanks Jim. It works fine.
| ....considering that "aa" limits the user to enter just the letters 'aa',
| what should be the input mask should I need to allow any alphabetic
| character, that is, a-z?
|
| cheers
|
| "Jim Rech" wrote:
|
| You might try this validation formula, where the validation cell is A1:
|
| =AND(LEN(A1)=7,LEFT(A1,2)="aa",RIGHT(A1,1)="a",VAL UE(MID(A1,3,4)))
|
| --
| Jim
| "archiboy" wrote in message
| ...
| | when entering data in a cell, i need to restrict users to enter the
| following
| | alphanumeric combination and nothing more.
| |
| | "aa####a", where "a" is an alphabetic character (a-z) and "#" is a
number
| | (0-9)
| |
| | i cannot figure out the formula to use under the 'custom' section of
| Data -
| | Validation
| |
| | pls help! thanks
|
|
|