One play which might suffice ..

Set-up a defined columnar range in say, X1:X4
---------------
Input in X1:X4 the letters: A, B, C, D
Select X1:X4, and click inside the namebox
(the droplist just to the left of the equal sign)
and type: List

(Note that the defined range: List can be set-up on another sheet)

Suppose the col to be formatted is col A
Select col A (select the col header)
Click Data Validation
Select Custom under "Allow" droplist
Put in "Formula:" box
=AND(LEN(A1)=9,ISNUMBER(MID(A1,3,6)+0),ISNUMBER(MA TCH(RIGHT(A1,1),List,0)))
Click OK

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Luke" wrote in message
...
Hi

Is it possible to only allow data of a certain format to be entered in to
a
cell. For instance a UK national insurance number that always has the
same
format of two letters followed by six numbers and then either A, B, C or D
i.e YB123456A.

Many thanks for your help

Luke