Is this number validation possible?

[Forum freak \(at work\)]

Hi

I need to validate the contents of a cell based on the following.

It must be a 5 digit number.
The 5 numbers can only be 1,2,3,4, & 5
Each number can only be used once.

This will be a test included in a macro however could it be done by formula?

Any help would be appreciated.

Kenny W
Using XP Pro and Excel 2003

 

[Joel]

Function ValidateNum(Target As Range)

Num = Target.Text
ValidateNum = True
If Len(Num) <> 5 Then
ValidateNum = False
Else
For i = 1 To 5
char = Mid(Num, i, 1)
If Asc(char) < Asc("0") Or _
Asc(char) > Asc("9") Then

ValidateNum = False
Exit For
End If
Next i
End If

End Function

 

[Mike H]

Hi,

Try this

Function IsValid(Target As Range)
MyString = Target.Text
IsValid = True
If Len(MyString) <> 5 Then
IsValid = False
Else
For x = 1 To 5
MyChar = Mid(MyString, x, 1)
newstring = WorksheetFunction.Substitute(MyString, Mid(MyString, x,
1), "")
If Asc(MyChar) < Asc("0") Or Asc(MyChar) > Asc("5") Or Len(newstring)
< 4 Then
IsValid = False
Exit For
End If
Next x
End If
End Function


Mike

 

[Alan Moseley]

In answer to your question, yes it can be done by a formula, which is
something like:-

=IF(AND(LEN(A1)=5,NOT(ISERROR(FIND("1",A1))),NOT(ISERROR(FIND("2",A1))),NOT(ISERROR(FIND("3",A1))),NOT(ISERROR(FIND("4",A1))),NOT(ISERROR(FIND("5",A1)))),TRUE,FALSE)

Do I win?

 

[Mike H]

No,

You could have done this

=IF(AND(LEN(A1)=5,NOT(ISERROR(FIND({"1","2","3","4","5"},A1)))),TRUE,FALSE)

Mike

 

[Forum freak \(at work\)]

Yes these did the trick.

As to "Who is the winner" What is the criteria for a winner, speed, maybe
accuracy.
Perhaps I should win with most gratitude!!!

Kenny W

 

[Ron Rosenfeld]

No,

You could have done this

=IF(AND(LEN(A1)=5,NOT(ISERROR(FIND({"1","2","3","4","5"},A1)))),TRUE,FALSE)

Mike

That's OK for just a formula in a cell, but you can't use an array constant in
a Data Validation custom formula.
--ron

 

[Forum freak \(at work\)]

Many thanks Ron

Luckily I am just using the formula in a cell!

Regards
Kenny W