Cell validation

[T. Valko]

Still chokes:

?A?c*0000?
*****1234*
**a??0000x

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

Accepts lower case letters:

aBcDe9999F
aaaaa0000a

--
Biff
Microsoft Excel MVP


message news:[email protected]...

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

Those INDIRECTs make it robust against row insertions.

If rows will *never* be inserted then, yes, we can do without them.

 

[T. Valko]

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]...

 

[Rick Rothstein \(MVP - VB\)]

True, but they also make the formula volatile as well.

Rick

 

[KRISHNA]

Hi,
Would need a macro to restrict a set of cells with only alphabets of length 3 to 35

 

[isabelle]

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 :