Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|