Data Validation-Input Mask

S

Smitty52

I want to restrict entries to an Excel Cell to 6 characters, the first two
must be capitalized alpha characters and the following four can be any digit.
I know I can probably do this in Access, but how do i do it in Excel?
I have Office 2003
 
A

Andy Wiggins

Data > Validation will do (most of) this.

On "Allow" choose "Text Length" and set the minimum and maximum values.
Your user will only be allowed to enter text within the values set.

Why, "most of"? Because the user can try and type in as much as he wants;
only when he presses "Enter" will the restriction be invoked.
 
B

Biff

Data > Validation will do (most of) this.

Data > Validation will do all of this.

It ain't pretty:

=AND(LEN(A1)=6,CODE(LEFT(A1))>=65,CODE(LEFT(A1))<=90,CODE(MID(A1,2,1))>=65,CODE(MID(A1,2,1))<=90,COUNT(--MID(A1,ROW(INDIRECT("3:6")),1))=4)

Length must be exactly 6 characters
The first 2 characters must be uppercase alpha characters A to Z
The next 4 characters must be numbers 0 to 9

Of course, data validation is easily defeated by copy/pasting or dragging.

Biff
 
D

Dave Peterson

This array formula didn't work for me as the data|validation formula. I could
use it in another cell, then use that cell as the basis for data|validation
though.

Since there's only 4 digits, maybe it's better to just bite the bullet and check
each of those characters to make sure that they're between 48 and 57
(inclusive)--just like you did with the uppercase letters?
 
B

Biff

Works just fine for me.

If you get a message, "The formula currently...........do you wish to
continue?", just answer YES.

I know you don't do sample files but just in case......(you can trust me!)

Sample file: Validation.xls 13.5kb

http://cjoint.com/?iDdvEeT6lo

Now, after all is said and done, I'm having second thoughts as to whether or
not this is really what the OP wanted. They may want the validation but that
term, Input Mask, has a different meaning!!!

Biff
 
Top