custom data validation

[Paul James]

Is there a way to set custom data validation criteria in a cell so that it
will only accept entries in the following form:

1 letter, followed by
3 numbers, followed by
3 letters, followed by
3 numbers.

In other words, entries of the form

A787MLV978 or
B676GHW089

There are always exactly 10 characters and no spaces.

Can anyone tell me what I should put in the Formula box in the Data
Validation dialog so that entries will be required in that format?

Thanks in advance.

Paul

 

[Peo Sjoblom]

You could try something like

=AND(LEN(TRIM(CLEAN(A1)))=10,ISERR(--TRIM(CLEAN(LEFT(A1)))),ISNUMBER(--TRIM(
CLEAN(MID(A1,2,3)))),ISERR(--TRIM(CLEAN(MID(A1,5,3)))),ISNUMBER(--TRIM(CLEAN
(RIGHT(A1,3)))))

not tested except for your example

 

[Paul James]

Thanks for your reply, Peo.

I tried the formula you suggested (below) and unfortunately, it doesn't let
me enter characters in the format I described. Can you think of anything in
that expression that might be preventing that?

Two questions about your expression:

1. I think I know what ISNUMBER does - it tests to see if the character in
the location specified by the MID function is a number. Bt what does ISERR
do?

2. What do the double hyphens before the TRIM functions do? I've not seen
that notation before.

Thanks

 

[Peo Sjoblom]

It certainly worked for me. I just tested it

A787MLV978
B676GHW089
A123ERT789
E789TYU125
W000QQQ111

I could enter all the above combinations

when I tried

1TYU888PPP

or

space123AAA789

I was denied.

Of course you could enter a text character like
"
instead of a letter and you would need to add a few extra lines
to guard against that..

Do you know how to apply the validation?

 

[icestationzbra]

peo is right. it works for the examples provided in your first post.

you may not have applied the validation correctly, as peo hinted.

select a range of cells where you want this validation performed.

go to DATA > VALIDATION.

select CUSTOM from Validation Criteria - Allow.

paste the formula provided by peo, without the equals '=' sign.

entering values over the range selected for validation.

 

[icestationzbra]

peo,

i am afraid, i spoke too soon. paul may be right.

when i copy the values and paste it onto the cells primed for
validation, it does not give me an error. however if i enter a value
manually, it would not allow me to enter even a legal value.

i should have tested it a little more before posting my earlier
feedback. me bad...

mac.

 

[Paul James]

Thanks for your observation, Mac. Yes, there are ways around data
validation -

1. entering the same value in several cells at the same time with
Ctrl-Enter, where not all the cells have the same validation code
2. entering the value with VBA
3. pasting the value in from the clipboard.

It seems that to use Data Validation, you need to type the value into the
cell from the keyboard.

So back to my original question: can anyone tell me how to validate the
format I described in my original post?

Peo - it looks like you might be close to the solution - any idea how it
could be modified to work?

Paul

 

[Peo Sjoblom]

Not if pasting is a possibility, you could use VBA but that can be
worth nothing if the user will not enable it when he/she opens the wb

 

[Gord Dibben]

mac

That is normal behaviour for pasted cells. DV will error out only when
manually entering.

Gord Dibben Excel MVP

 

[Paul James]

Let's ignore the problem with users bypassing the validation by pasting in
the data. Does anyone know what I can put in the validation criteria to
filter out the wrong data?

Again, I'm trying to validate strings in the following format:

1 letter, followed by
3 numbers, followed by
3 letters, followed by
3 numbers.

In other words, entries of the form

A787MLV978 or
B676GHW089

There are always exactly 10 characters and no spaces.

Thanks again in advance.

Paul