Data Validation - Custom Criteria for two numbers

[MissDB]

I have a spreadsheet where I have specific cells that need to be either 9 characters in length or 11 characters in length.

Currently I am using Data Validation for Text Length Between 9 and 11.

The issue here is that someone could potentially put in 10 characters and this would then cause problems with some macros.

Is there a formula I could use in the Custom Criteria option that would enable me to specify that only text length of 9 OR 11 is allowed?

[sandy666]

try

Please Login or Register  to view this content.

[bebo021999]

With A1 is input cell, stay in cell A1.

Instead of Text Length, go into custom:

Formula inbox:

=OR(LEN(A1)=9,LEN(A1)=11)

[MissDB]

Hey Sandy, I think I love you!!! I should have been able to work that out as I've used LEN before but it was baffling me this morning!

Thanks again.

[MissDB]

Sorry one more quick question. I have multiple cells and they start at C18, then C25 etc, Does the A1 reference need to change?

[sandy666]

Glad to help
Mark thread as solved, add rep
Thank you.

[sandy666]

DV works on A1, so if you want DV on another cell (s) you need change ref

[MissDB]

Do I have to do that on every single cell? I have a lot of lines to add the validation to.....Is there another way to do it?

[sandy666]

If cells are in range like A1:A10, you can put DV into A1 and drag it down to A10. If there are single cells you need do DV one by one

[MissDB]

Thank you for your help

[sandy666]

You are welcome