Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LIMITING DATA VALIDATION ENTRIES
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! -- William |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LIMITING DATA VALIDATION ENTRIES
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 C11 =SUMPRODUCT((CODE(UPPER(MID($C$11,ROW(1:$10),1))) =65)*(CODE(UPPER(MID($C$11,ROW(1:$10),1)))<=90)+(C ODE(UPPER(MID($C$11,ROW(1:$10),1)))=48)*(CODE(UPP ER(MID($C$11,ROW(1:$10),1)))<=57))=10 -- John C "William" wrote: 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! -- William |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Data Validation and limiting characters | Excel Worksheet Functions | |||
Limiting Entries in A List Box | Excel Worksheet Functions | |||
Limiting Validation entries to 2 places after a decimal | Excel Discussion (Misc queries) | |||
Data Validation for Weekday entries only | Excel Worksheet Functions | |||
Using Data Validation - how do I allow other entries | New Users to Excel |