Validation Rule

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
I am using an input mask of ">aaaaaaaaa" for a text field that I want to only accept letters a-Z and and digits 0-9 (no spaces or symbolics). But I can't quite get the validation rule right. Any thoughts?
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi
Try AAAAAAAAA for 9 compulsory characters and / or digits. Change to lower case for optional.
Andrew
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Two issues I ran into with that. It allows spaces and if you use it as an input mask you cannot give a friendly error.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
The lower case 'a' allows spaces but the upper case doesn't. The lower case example means the user doesn't have to enter all of the characters - that's why you are seeing that anomaly. I agree the error messages are a bit unhelpful for the average user. If you want a variable length without spaces and custom error messages you may want to write your own validation rule using VBA on the BeforeUpdate event.

HTH, Andrew
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

*sigh* That was my conclusion too. I was just hoping someone knew something that could be done at the table level. Appreciate the input :)
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
If the first n characters are compulsory, how about something like >AAAaaaaaa? That way you won't have any spaces in the first n characters.
Andrew
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

I just changed it to a fixed length all numeric field and avoided the issue all together :banghead:
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
That IS good info (thank you), but I was already aware of it's information. It does not, unfortunatly, cover what I was trying to do. The origianl goal was to create an input mask or data validation rule that allows alphas & numerics but not symbolics or spaces while still retaining a variable length structure. I actually found a decent workaround I think with using an input mask of "aaaaaaaaa" then using the validation rule of Instr([MyField]," ") = 0. This combination seems to have accomplished the goal mostly.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,790
Messages
5,544,296
Members
410,601
Latest member
Silver2
Top