Data Validation-Input Mask
 

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

Data Validation-Input Mask
 

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.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Smitty52" wrote in message
...
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

Data Validation-Input Mask
 

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,COU NT(--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

"Andy Wiggins" <contact me via my website at www.BygSoftware.com wrote in
message ...
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.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Smitty52" wrote in message
...
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

Data Validation-Input Mask
 

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?

Biff wrote:

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,COU NT(--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

"Andy Wiggins" <contact me via my website at www.BygSoftware.com wrote in
message ...
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.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Smitty52" wrote in message
...
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



--

Dave Peterson

Data Validation-Input Mask
 

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

"Dave Peterson" wrote in message
...
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?

Biff wrote:

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,COU NT(--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

"Andy Wiggins" <contact me via my website at www.BygSoftware.com wrote
in
message ...
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.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Smitty52" wrote in message
...
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



--

Dave Peterson

Data Validation-Input Mask
 

Oops.

Poor testing on my part!

I used lower case letters in my test. Doh!!!!



Biff wrote:

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

"Dave Peterson" wrote in message
...
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?

Biff wrote:

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,COU NT(--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

"Andy Wiggins" <contact me via my website at www.BygSoftware.com wrote
in
message ...
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.

--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Smitty52" wrote in message
...
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



--

Dave Peterson

--

Dave Peterson