Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Valided alphanumeric value
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Valided alphanumeric value
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: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Valided alphanumeric value
=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","N ot OK")
ctrl+shift+enter, not just enter "Montu" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Valided alphanumeric value
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) -- Biff Microsoft Excel MVP "Montu" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ALPHANUMERIC | Excel Worksheet Functions | |||
How to add alphanumeric data? (e.g., 1C+2D) | Excel Discussion (Misc queries) | |||
alphanumeric range | Excel Worksheet Functions | |||
alphanumeric sort | Excel Discussion (Misc queries) | |||
Sorting alphanumeric | Excel Discussion (Misc queries) |