Valided alphanumeric value

[Montu]

I want to valided or check of a cell value which content alphanumeric. the
features of alphanumeric value is -
1) lenth should not exceed 10
2) First five character should be alphabatic, next four character should be
numeric & last character should be alphabetic. as example - ABCDE1234E
So is there any way to do the same.
Thanks in advance.

 

[Stefi]

Try this UDF either in a cell or in a custom validation formula:
Function Val_AN(ANstring)
Val_AN = True
If Len(ANstring) <> 10 Then
Val_AN = False
Else
For d = 1 To 10
If d <= 5 Or d = 10 Then
If Mid(ANstring, d, 1) < "A" Or Mid(ANstring, d, 1) > "Z" Then
Val_AN = False
Exit For
End If
Else
If Mid(ANstring, d, 1) < "0" Or Mid(ANstring, d, 1) > "9" Then
Val_AN = False
Exit For
End If
End If
Next d
End If
End Function

Regards,
Stefi


„Montu†ezt írta:

 

[Teethless mama]

=IF((LEN(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))-1))=5)*(ISNUMBER(SUM(MID(A1,ROW(INDIRECT("6:8")),1)+0)))*(LEN(A1)=10)*(ISERR(RIGHT(A1,1)+0)),"OK","Not OK")

ctrl+shift+enter, not just enter

 

[T. Valko]

If you want this as a data validation rule copy/paste the formula into the
data validation refedit. If you want this as a worksheet function enter the
formula as an array ** :

=SUM(5-LEN(SUBSTITUTE(UPPER(LEFT(A1,5)),CHAR(ROW(INDIRECT("65:90"))),"")),COUNT(--MID(A1,6,4)),COUNT(SEARCH(CHAR(ROW(INDIRECT("65:90"))),RIGHT(A1))))=7

Meets these conditions:

Length must be 10 chars long
The first 5 chars must be A-Z or a-z only
The 6th to 9th chars must be the digits 0-9 only
The 10th char must be A-Z or a-z only

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)