Restrict data entry to uppercase text with a length of 3 to 6 letters
Posted by John Zeis on January 22, 2002 7:32 AM
I want to restrict data entry in a cell to uppercase text with a length of 3 to 6 characters. I also want to automatically convert any entered lowercase letters into uppercase letters. The input data for this cell must be uppercase alphabetic with a length of 3 to 6 characters.
Posted by Mark W. on January 22, 2002 8:41 AM
Here's the Data Validation...
formula that will insure upper case and proper
string length upon data entry...
=AND(LEN(A1)>2,LEN(A1)<7, CODE(MID(A1,LEN(A1),1))>64, CODE(MID(A1,LEN(A1),1))<91)
Please note that as constructed this formula will
only allow the entry of alphabetic characters.
Posted by Steve Hartman on January 22, 2002 9:16 AM
this doesn't work
This formula will allow lowercase as lon as the last character is uppercase. To get all upper use:
=AND(LEN(A1)>2,LEN(A1)<7, EXACT(UPPER(A1),A1))
This formula should be entered in the data validation as a custom formula.
Posted by Mark W. on January 22, 2002 11:49 AM
You're right... I didn't test enough cases...
...a fix for my approach would require an array
formula which isn't permitted by Data Validation.
Your solution if quite elegant!!
Posted by IML on January 22, 2002 12:03 PM
You're right... I didn't test enough cases...
Although it doesn't check for numbers. I swore I entered an array in conditional formatting once, I thought it was just no add in formulas. Hmm, time to play.
Posted by Steve Hartman on January 22, 2002 12:07 PM
Re: You're right... I didn't test enough cases...
And you're right, I forgot to check for #'s. Back to the drawing board.
Posted by Aladin Akyurek on January 22, 2002 12:32 PM
Re: You're right... I didn't test enough cases...
Steve --
That requires a small change. And, I agree with Mark, that use of EXACT is elegant.
=AND(LEN(A1)>2,LEN(A1)<7,OR(ISNUMBER(A1), EXACT(UPPER(A1),A1)))=TRUE
=AND(LEN(A1)>2,LEN(A1)<7,OR(ISNUMBER(A1), EXACT(UPPER(A1),A1)))+0=1
That last bit with TRUE or 1 is needed, I think.
Aladin
Posted by Steve Hartman on January 22, 2002 12:39 PM
Still not there
Thank you, both of you. Hope it makes up for the brain hiccup on the =SUBSTITUTE question above
This solution takes care of an entry that is all numbers, but mixed alpha and numbers like FF6D5 is still accepted.
Posted by IML on January 22, 2002 12:58 PM
Array
If I knew the right array, I think it would work in conditional formatting.
Take a data set of
{"bird","dog",8;"bird","dog",1;"jack","jill",4}
Where you want to highlight if bird & dog = 9
Go to formula enter in
=SUM(($A$1:$A$3="bird")*($B$1:$B$3="dog") * ($C$1:$C$3))=9
Now select your formatting.
Now go back to formula box, hit control shift enter and it works. Maybe the limit is you can only do this on this on the first condition/formula?
Posted by IML on January 22, 2002 1:04 PM
I don't where this conditional formatting came from. nevermind...
Posted by Aladin Akyurek on January 22, 2002 1:06 PM
Re: Still not there
Wait. My change allows for pure numbers. If pure numbers are not alloed, they should be canged to:
=AND(LEN(A1)>2,LEN(A1)<7,ISTEXT(A1), EXACT(UPPER(A1),A1))=TRUE
They don't excude entries like QWS4U of course. That is a bit trickier.
Posted by Steve Hartman on January 22, 2002 1:26 PM
Re: Still not there
The original question stated he only wanted uppercase alphabetic characters to be accepted. I think this is going to be a macro solution instead of a simple data validation formula. (Or an entry for the horror story page!). I thought I had it with this ugly formula:
=AND(LEN(A1)>2, LEN(A1)<7,EXACT(UPPER(A1),A1), OR(ISNUMBER(VALUE(MID(A$1,1,1))), ISNUMBER(VALUE(MID(A1,2,1))), ISNUMBER(VALUE(MID(A1,3,1))), ISNUMBER(VALUE(MID(A1,4,1))), ISNUMBER(VALUE(MID(A1,5,1))), ISNUMBER(VALUE(MID(A1,6,1))))=FALSE)
It strips each of the six possible characters in the cell and checks for proper length, checks for uppercase and and checks for a number. It works just fine except it accepts FF#R and DG$H. So close and yet so far.
Posted by Juan Pablo G. on January 22, 2002 1:36 PM
Re: Still not there
In my tests (In '97) i think i found out that data validation doesn't accept UDF's, is this correct ?
Anyway, here's what I did.
First, the NoNumbers UDF, to strip any characters out of the string (Can be adapted to include $,%,& and anything else), and second, the MyCondition UDF, which is basically the same formula that has been around, but in VBA.
I assumed the data was gonna be entered in Column A. I enter in ColumnB
=MyCondition(A2) and drag down. 'This could be done directly in Validation, but didn't work, avoiding the extra column...
Now, select Column A, go to Data, Validation and put as condition
=B2
That worked..
Juan Pablo G.
'Code here...
Function NoNumbers(Rng As Range) As String
Dim T As String
Dim i As Byte
T = Rng.Value
For i = 0 To 9
T = Application.Substitute(T, i, "")
Next i
NoNumbers = T
End Function
Function MyCondition(Rng As Range) As Boolean
MyCondition = (Len(Rng) > 2) * (Len(Rng) < 7) * (Len(Rng) = Len(NoNumbers(Rng))) * (InStr(1, Rng, UCase(Rng), 0) = 1)
End Function
