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 |
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 |
All times are GMT +1. The time now is 10:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com