Validation

[Guest]

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

 

[Arvi Laanemets]

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

 

[Guest]

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

 

[Guest]

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]

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