Cell validation

  • Thread starter Thread starter Sai Krishna
  • Start date Start date
I ***think*** this array-entered formula might work...

=AND(AND(MID(A1,ROW($1:$5),1)>="A",MID(A1,ROW($1:$5),1)<="Z"),AND(MID(A1,ROW($6:$9),1)>="0",MID(A1,ROW($6:$9),1)<="9"),AND(MID(A1,10,1)>="A",MID(A1,10,1)<="Z"),LEN(A1)=10)

Note for the archives: Commit this formula with Ctrl+Shift+Enter instead of
just Enter by itself.

Rick
 
Slightly shorter with 2 less function calls:

=SUM(LEN(A1)=10,5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(INDIRECT("65:90"))),"")),COUNT(-MID(A1,ROW(INDIRECT("6:9")),1))=4,LOOKUP(CODE(RIGHT(A1)),{65,90,91},{1,1,0}))=8
 
I allowed for that on purpose since the OP didn't specifically say to ignore
them (his original requirement was only for "alphanumeric" characters). On
top of that, the second formula that Bob posted, a modification of his
original formula in response to a comment by me, and which the OP indicated
"worked" for him, allowed for both upper and lower case letters... I figured
that was a confirmation that upper/lower case letters were both acceptable.
We will have to wait for the OP to come back to this thread and address this
issue before we can know for sure what his intention was. So, putting that
issue aside, do you think the formula works shape-wise? Or did I miss
something?

Rick
 
I'm not 100% sure, but I think you can remove those INDIRECT function
calls...

=SUM(LEN(A1)=10,5-LEN(SUBSTITUTE(LEFT(A1,5),CHAR(ROW(65:90)),"")),COUNT(-MID(A1,ROW(6:9),1))=4,LOOKUP(CODE(RIGHT(A1)),{65,90,91},{1,1,0}))=8

Giving it a quick test, it looks like this version of your formula
(array-entered, of course) works.

Rick
 
Those INDIRECTs make it robust against row insertions.

If rows will *never* be inserted then, yes, we can do without them.
 
do you think the formula works shape-wise?

Yeah, it works if case is not a consideration and rows will not be inserted.



--
Biff
Microsoft Excel MVP


message news:%[email protected]...
 
hi,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As String, res As String, i As Integer
Set rng = Range("A1:A10") 'Adapt this range as your wish
Set isect = Application.Intersect(Target, rng)
txt = Array( _
"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", _
"a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", _
"â", "à", "é", "è") ' you can add characters the way you want

If Not isect Is Nothing Then
For i = 1 To Len(Target)
c = Mid(Target, i, 1)
If IsError(Application.Match(c, txt, 0)) Then res = res & Mid(Target, i, 1) & ", "
Next
If Len(Target) > 2 And Len(Target) < 35 Then
If res = "" Then
Exit Sub
Else
MsgBox "The following characters are forbidden : " & res & Chr(10) & _
"Please start again" & Chr(10) & _
"Don't forget : only alphabets of length 3 to 35"
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
Exit Sub
End If
Else
MsgBox "Don't forget : only alphabets of length 3 to 35"
End If
End If
End Sub



--
isabelle



Le 2012-01-06 04:49, KRISHNA a écrit :
 
Back
Top