Validation

J

JohnUK

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
 
A

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
 
J

JohnUK

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
 
J

JohnUK

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top