Data Validation

[MahaRaj]

HI
I am trying to put data validation in a cell where I want in the data in
this format. like
AB123456C as national insurance.
I tried CUSTOM in data validation and put there ??######?
It does not work.
any suggestion here?

Office 2003
winXP HE SP1

Thanks
--
MahaRaj
==================================================================
Sorry about the time thingy.
Not my fault. My ISP ntl don't care whatever you think. Just follow up the
damn query. Check massage harder before wasting your time on sending me a
reminder.
Just kidding. Friends?

 

[MahaRaj]

HI
I am trying to put data validation in a cell where I want in the data in
this format. like
AB123456C as national insurance.
I tried CUSTOM in data validation and put there ??######?
It does not work.
any suggestion here?

Office 2003
winXP HE SP1

Thanks
--
MahaRaj
==================================================================
Sorry about the time thingy.
Not my fault. My ISP ntl don't care whatever you think. Just follow up the
damn query. Check massage harder before wasting your time on sending me a
reminder.
Just kidding. Friends?

Anybody?? Please!

 

[Dave Peterson]

what's ?? and ?.

I guessed alphas: A-Z or a-z.

=AND(LEN(A1)=9,CODE(LEFT(UPPER(A1),1))>64,CODE(LEFT(UPPER(A1),1))<91,
CODE(MID(UPPER(A1),2,1))>64,CODE(MID(UPPER(A1),2,1))<91,
ISNUMBER(-MID(A1,3,6)),MID(A1,3,6)=TEXT(--MID(A1,3,6),"000000"),
CODE(RIGHT(UPPER(A1),1))>64,CODE(RIGHT(UPPER(A1),1))<91)

 

[Debra Dalgleish]

For data validation in cell A2:

Choose Data>Validation
For Allow, select Custom
In the formula box, enter:

=AND(LEN(A2)=9,CODE(LEFT(A2,1))>=65,CODE(A2)<=90,
CODE(MID(A2,2,1))>=65,CODE(MID(A2,2,1))<=90,
ISNUMBER(MID(A2,3,6)+0),CODE(RIGHT(A2,1))>=65,
CODE(RIGHT(A2,1)<=90))

 

[Dave Peterson]

AB1234E1C
(CQC Southern Region, hehehe)

For data validation in cell A2:

Choose Data>Validation
For Allow, select Custom
In the formula box, enter:

=AND(LEN(A2)=9,CODE(LEFT(A2,1))>=65,CODE(A2)<=90,
CODE(MID(A2,2,1))>=65,CODE(MID(A2,2,1))<=90,
ISNUMBER(MID(A2,3,6)+0),CODE(RIGHT(A2,1))>=65,
CODE(RIGHT(A2,1)<=90))

 

[Debra Dalgleish]

Rats!

AB1234E1C
(CQC Southern Region, hehehe)