LIMITING DATA VALIDATION ENTRIES

[William]

I have a column that needs to be exactly 10 characters long, with some of the
characters alpha and some numeric. I can do this easily enough be selecting
Data/Validation/Text/equals/10. But is there a way that I can ALSO limit the
characters entered to alpha numeric, but without allowing "funky" characters
such as those on the top row of the keyboard: !@#$%^&*()_+?

Thanks for ideas!

 

[John C]

Bit windy, but it does work. This effectively limits to A-Z and 0-9, and must
be 10 characters. When you first put the formula into the Data|Validation,
Custom, it will ask you if it is ok that it validates to an error, choose OK,
and go from there.
NOTE: I would use the 2nd tab to maybe give instructions to the user as to
the proper entry format.
NOTE2: There isn't an 'effective' way to prevent someone from copy and paste
into a DV cell, though I had posted sometime back on how to 'force'/coerce
the user to get it fixed.

Hope it helps!
This is assuming your DV is in cell C1
=SUMPRODUCT((CODE(UPPER(MID($C$11,ROW(1:$10),1)))>=65)*(CODE(UPPER(MID($C$11,ROW(1:$10),1)))<=90)+(CODE(UPPER(MID($C$11,ROW(1:$10),1)))>=48)*(CODE(UPPER(MID($C$11,ROW(1:$10),1)))<=57))=10