Validation - Precede with LR and between 6 and 9 characters long.

F

forevertrying

Hi,

I currently have a validation rule running whereby it will only allow a code
that starts with LR and is 6 character long i.e.

LR8976

I need it to allow for either 6 or 9, or allow for between 6 and 9.
Preferably one or the other as it leaves less room for error.

Can you help?
 
D

Dave

Hi,
If you post your current data validation formula, we could probably help you
modify it.
Regards - Dave.
 
K

Ken Johnson

Hi,

I currently have a validation rule running whereby it will only allow a code
that starts with LR and is 6 character long i.e.

LR8976

I need it to allow for either 6 or 9, or allow for between 6 and 9.
Preferably one or the other as it leaves less room for error.

Can you help?
Click to expand...

Maybe...

for either 6 or 9

=AND(CODE(A1)=76,CODE(MID(A1,2,1))=82,OR(LEN(A1)=6,LEN(A1)=9))

or, for between 6 and 9

=AND(CODE(A1)=76,CODE(MID(A1,2,1))=82,LEN(A1)>=6,LEN(A1)<=9)

assuming entry into A1.

Ken Johnson
 
F

forevertrying

Hi Ken,

Will this notice whether or not there is an "LR" at the beginning?
 
K

Ken Johnson

Hi Ken,

Will this notice whether or not there is an "LR" at the beginning?
Click to expand...

Hi,

If A1 doesn't start with uppercase L then CODE(A1) = 76 is FALSE and
entry is not valid. CODE(A1) returns the ASCII code of only the first
character in A1.
Similarly, if the second character in A1 is not uppercase R then
CODE(MID(A1,2,1))=82 is FALSE and entry is not valid.

If uppercase is not essential then you could use...

=AND(LEFT(A1,2)="LR",OR(LEN(A1)=6,LEN(A1)=9))

OR

=AND(LEFT(A1,2)="LR",LEN(A1)>=6,LEN(A1)<=9)

because the LEFT function is not case sensitive.

Ken Johnson
 
F

forevertrying

Sorry, but half of that last one went straight over my head.

Firstly, A1... is that the cell reference? Mine would be b9 as that is where
I am trying to put the validation, is that right?

Secondly, do i need to sit and manually put each validation code in for each
cell I want it to apply to? Surely there's a quicker way??

Kind Regards
 
K

Ken Johnson

Sorry, but half of that last one went straight over my head.

Firstly, A1... is that the cell reference? Mine would be b9 as that is where
I am trying to put the validation, is that right?

Secondly, do i need to sit and manually put each validation code in for each
cell I want it to apply to? Surely there's a quicker way??

Kind Regards
Click to expand...

Hi,

I was assuming that the cell to which data validation was being
applied was A1, only because there was no mention of the cell or cells
you were working with.

You say you are trying to put the validation in B9, so just replace
all of the A1s with B9.

No you don't have to go through the same tedious steps for every cell
separately. You first select all of the cells that you are wanting to
be validated before going Data|Validation to bring up the Data
Validation dialog.
When you select more than one cell, all of the selected cells bar one
are shaded.
The unshaded cell is referred to as the active cell.
For example, if you click on B9 then drag down to B18, B10:B18 will be
shaded and B9 will be unshaded because it is the active cell.
Another example, if you hold down the Ctrl key while you click B9,
then D9 (no dragging, just clicking), then F9 in that order; B9 and D9
will be shaded and F9 will be unshaded because it is the active cell.
So, after you have selected all of the cells to be validated with the
same Custom Formula, and you have noted the address of the active
cell, you use the active cell's address when typing the Custom Formula
into the Formula: box on the Data Validation dialog.
Excel automatically adjusts that address for each of the validated
cells.
An important thing to remember is to use the relative column and
relative row form of the active cell's address ie no $ signs.
Excel doesn't change the address' column label if there is a $ sign
before it, and the same goes for the row label.
So, if B9 turns out to be the active cell after you have selected all
the cells to be validated then use B9 in the formula.
If the cells to be validated are not contiguous (selected using Ctrl-
Click) then the active cell will be the last cell you Ctrl-Click and
you will need to use its address in the formula.

Ken Johnson
 
Top