Hi,
I need a way to stop anything from being entered into a cell other than 2
letters and 2 numbers eg:
AA12 (Alpha Alpha Numeric Numeric)
Is there a way using validation to do this?
Many thanks
John

Hi

P.e.
=AND(ISERROR(MID(A1,1,1)*1),ISERROR(MID(A1,2,1)*1) ,NOT(ISERROR(MID(A1,3,1)*1)),NOT(ISERROR(MID(A1,4, 1)*1)),LEN(A1)=4)
as validation rule


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"JohnUK" wrote in message
...
Hi,
I need a way to stop anything from being entered into a cell other than 2
letters and 2 numbers eg:
AA12 (Alpha Alpha Numeric Numeric)
Is there a way using validation to do this?
Many thanks
John

Hi Arvi,
Thank you for your response.
I tried entering the formula into the Data Validation box on the Custom box,
is this correct, or is there another way? because as it is I cant get it to
work.
John

"Arvi Laanemets" wrote:

Hi

P.e.
=AND(ISERROR(MID(A1,1,1)*1),ISERROR(MID(A1,2,1)*1) ,NOT(ISERROR(MID(A1,3,1)*1)),NOT(ISERROR(MID(A1,4, 1)*1)),LEN(A1)=4)
as validation rule


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"JohnUK" wrote in message
...
Hi,
I need a way to stop anything from being entered into a cell other than 2
letters and 2 numbers eg:
AA12 (Alpha Alpha Numeric Numeric)
Is there a way using validation to do this?
Many thanks
John

Hi

It works for me.

I selected cell A1, from Data menu Data Validation, selected Custom, and
entered the formula into Formula field (copied from formula toolbar, as I
tested it in some cell before)

Copy the formula into any cell except A1. The formula must return True, when
A1 is empty, or contains string in format "CC##", otherwise it always
returns False


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )


"JohnUK" wrote in message
...
Hi Arvi,
Thank you for your response.
I tried entering the formula into the Data Validation box on the Custom
box,
is this correct, or is there another way? because as it is I cant get it
to
work.
John

"Arvi Laanemets" wrote:

Hi

P.e.
=AND(ISERROR(MID(A1,1,1)*1),ISERROR(MID(A1,2,1)*1) ,NOT(ISERROR(MID(A1,3,1)*1)),NOT(ISERROR(MID(A1,4, 1)*1)),LEN(A1)=4)
as validation rule


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"JohnUK" wrote in message
...
Hi,
I need a way to stop anything from being entered into a cell other than
2
letters and 2 numbers eg:
AA12 (Alpha Alpha Numeric Numeric)
Is there a way using validation to do this?
Many thanks
John

Sorry Arvi,
Stupid of me - I didnt reference the cell I was working on.
Many thanks for your help It worked a treat.
John


"Arvi Laanemets" wrote:

Hi

P.e.
=AND(ISERROR(MID(A1,1,1)*1),ISERROR(MID(A1,2,1)*1) ,NOT(ISERROR(MID(A1,3,1)*1)),NOT(ISERROR(MID(A1,4, 1)*1)),LEN(A1)=4)
as validation rule


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"JohnUK" wrote in message
...
Hi,
I need a way to stop anything from being entered into a cell other than 2
letters and 2 numbers eg:
AA12 (Alpha Alpha Numeric Numeric)
Is there a way using validation to do this?
Many thanks
John