Data Validation

M

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?
 
M

MahaRaj

MahaRaj said:
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?
Click to expand...



Anybody?? Please!
 
D

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)
 
D

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))
 
D

Dave Peterson

AB1234E1C
(CQC Southern Region, hehehe)



Debra said:
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))
Click to expand...
 
Top