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" wrote in message
...
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!

what's ?? and ?.

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

=AND(LEN(A1)=9,CODE(LEFT(UPPER(A1),1))64,CODE(LEF T(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)



MahaRaj wrote:

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?

--

Dave Peterson

For data validation in cell A2:

Choose DataValidation
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))

MahaRaj wrote:
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


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

AB1234E1C
(CQC Southern Region, hehehe)



Debra Dalgleish wrote:

For data validation in cell A2:

Choose DataValidation
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))

MahaRaj wrote:
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

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

--

Dave Peterson

Rats!

Dave Peterson wrote:
AB1234E1C
(CQC Southern Region, hehehe)



Debra Dalgleish wrote:

For data validation in cell A2:

Choose DataValidation
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))

MahaRaj wrote:

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

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html