Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 119
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,358
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Data Validation and limiting characters MagnoliaSouth Excel Worksheet Functions 2 October 2nd 06 02:52 AM
Limiting Entries in A List Box Dave C Excel Worksheet Functions 1 January 3rd 06 07:55 PM
Limiting Validation entries to 2 places after a decimal Tim Excel Discussion (Misc queries) 9 October 28th 05 01:31 AM
Data Validation for Weekday entries only Casey Excel Worksheet Functions 2 August 11th 05 06:01 PM
Using Data Validation - how do I allow other entries DianeMcP New Users to Excel 4 July 19th 05 05:51 PM


All times are GMT +1. The time now is 02:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"