Limiting text entries
ASCII <91 (formula is correct)
"Joerg Mochikun" wrote in message
...
And here is the formula for column 2:
=AND(LEN(B1)=6,ISNUMBER(RIGHT(B1,5)*1),ISTEXT(LEFT (B1,1)),CODE(B1)<91)
Four statements in the AND function, separated by commas check if
1) Entry is 6 characters long
2) Last 5 characters evaluate as number (has to be multiplied by 1,
otherwise Excel regards it as text)
3) Left character is text
4) Left character is uppercase (ASCII < 92)
Cheers,
Joerg Mochikun
"Joerg Mochikun" wrote in message
...
As a start for column 1:
For A1 apply the custom validation formula
=MID(A1,LEN(A1)-1,1)=","
This will allow only entries where the second from last character is a
comma.
You can use the AND function to combine more criteria in your formula and
make it as sophisticated as you like.
Joerg
"Help4me" wrote in message
...
I have two columns in which I would like to limit the way text is input:
1) Lastname,First initial with no spaces (i.e., Smith,R)
2) One capital letter followed by five digits (i.e., D12345)
I do not have any ideas for column 1.
I can use the Limited Text validation feature for column 2, but that
only
limits the number of characters, not the type of characters input.
Thank you so much.
|