data validation and formatting

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?

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

"Laurie" <Laurie@discussions.microsoft.com> wrote in message
news:9242E174-54C6-4679-8A65-4BB0306988F2@microsoft.com...
>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?