data validation and formatting

[Guest]

I want users to be allowed to enter a seven character response in a cell.
The seven character response must be in the following format - number number
letter letter number number number. I specified the length of the entry by
using the data validation feature but I don't know how to ensure that the
response is in the right format. I'm using Microsoft Excel 2002. Any
suggestions?

 

[Biff]

Hi!

Kind of ugly but it seems to work:

Select the cell(s) in question. I'll use A1 in this example

Goto Data>Validation
Allow: Custom
Formula:

=AND(LEN(A1)=7,ISNUMBER(--LEFT(A1,2)),ISNUMBER(MATCH(CODE(UPPER(MID(A1,3,1))),ROW(65:90),0)),ISNUMBER(MATCH(CODE(UPPER(MID(A1,4,1))),ROW(65:90),0)),ISNUMBER(--RIGHT(A1,3)))

OK out

Biff