input masks and validation

[archiboy]

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

 

[Jim Rech]

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

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

--
Jim
| 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

 

[archiboy]

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]

This may do it:

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

--
Jim
| 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",VALUE(MID(A1,3,4)))
| >
| > --
| > Jim
| > | > | 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
| >
| >
| >