Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hello,
I am sending again my question or problem to you. i am not able to do so. please help me. I have one columen witch contain Ids 11 sifer in excel. i have one formula in javascript to validate id number. i have 3500 id numbers and i can not incert every number to check it is write or wrong. is there any way to find out or convert that javascript function into excel and get all wrong and right ids in next columen? Right IDs, 01014927367 01015726136 Wrong Ids 15010933773 45290122333 I got this function. <html <head <titlechecking ID number formula </title </head <body <form <INPUT NAME="ID" SIZE=11 maxlength=11 <INPUT TYPE="button" CLASS="button" NAME="bname" VALUE="ID Number" onClick="(ValidateID(this.form.ID.value))"<br </form <script type="text/jscript" function ValidateID(ID) { var TmpStr var Tmpr var vekt1 = "376189452" var vekt2 = "5432765432" var tot = 0 var base = 11 var value var vekt var TmpStr2 var Tmpr2 var vekt2 = "5432765432" var tot2= 0 var base = 11 // for first number for (i=0;i<9 ;i++) { value = ID.substr(i,1) vekt = vekt1.substr(i,1) tot = tot + (parseInt(value) * parseInt(vekt)) } i=tot/11 i=tot-(parseInt(i)*11) i=11-i TmpStr = ID TmpStr = ID.substr(9,1) Tmpr=(ID.length) // for second number for (y = 0; y < 10 ; y++) { value = ID.substr(y,1) vekt = vekt2.substr(y,1) tot2 = tot2 + (parseInt(value) * parseInt(vekt)) } y=tot2/11 y=tot2-(parseInt(y)*11) y=11-y TmpStr2 = ID TmpStr2 = ID.substr(10,1) Tmpr2=(ID.length) // integer check if (base!=Tmpr) { document.write("You must enter 11 number for ID (0 - 9) ") } else if ( parseInt(i)==TmpStr && parseInt(y)==TmpStr2) { document.write(ID +" " + "is right ID") } else { document.write("Obs!! You write wrong ID number!") } } </script </body </html best regards, -- Kamila |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
This involves two separate tasks:
1. translate the JavaScript into a simple, journalistic, English description of the algorithm desired. 2. implement the above in VBA If you can do the first, we can do the second. -- Gary's Student gsnu200706 "Kamila" wrote: Hello, I am sending again my question or problem to you. i am not able to do so. please help me. I have one columen witch contain Ids 11 sifer in excel. i have one formula in javascript to validate id number. i have 3500 id numbers and i can not incert every number to check it is write or wrong. is there any way to find out or convert that javascript function into excel and get all wrong and right ids in next columen? Right IDs, 01014927367 01015726136 Wrong Ids 15010933773 45290122333 I got this function. <html <head <titlechecking ID number formula </title </head <body <form <INPUT NAME="ID" SIZE=11 maxlength=11 <INPUT TYPE="button" CLASS="button" NAME="bname" VALUE="ID Number" onClick="(ValidateID(this.form.ID.value))"<br </form <script type="text/jscript" function ValidateID(ID) { var TmpStr var Tmpr var vekt1 = "376189452" var vekt2 = "5432765432" var tot = 0 var base = 11 var value var vekt var TmpStr2 var Tmpr2 var vekt2 = "5432765432" var tot2= 0 var base = 11 // for first number for (i=0;i<9 ;i++) { value = ID.substr(i,1) vekt = vekt1.substr(i,1) tot = tot + (parseInt(value) * parseInt(vekt)) } i=tot/11 i=tot-(parseInt(i)*11) i=11-i TmpStr = ID TmpStr = ID.substr(9,1) Tmpr=(ID.length) // for second number for (y = 0; y < 10 ; y++) { value = ID.substr(y,1) vekt = vekt2.substr(y,1) tot2 = tot2 + (parseInt(value) * parseInt(vekt)) } y=tot2/11 y=tot2-(parseInt(y)*11) y=11-y TmpStr2 = ID TmpStr2 = ID.substr(10,1) Tmpr2=(ID.length) // integer check if (base!=Tmpr) { document.write("You must enter 11 number for ID (0 - 9) ") } else if ( parseInt(i)==TmpStr && parseInt(y)==TmpStr2) { document.write(ID +" " + "is right ID") } else { document.write("Obs!! You write wrong ID number!") } } </script </body </html best regards, -- Kamila |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Gary''s Student is correct - we need an explanation of how the input number
is to be manipulated to get the test for correctness value out of it. I gave it a shot earlier in the day and was unable to get a valid ID result. Either my translation of some of the Java constructs is incorrect or there's a deeper problem. For example, the java construct substr(L,s) is, to my way of interpreting what I read about it the same as VB's Mid("text",s,L) where s is the starting character position and L is the length of the portion (# of characters). I also deduce that Java's parseInt(string) is the same as VB's Val(string). The loops, I thought were C-like with for(y=0; y<10,y++) being equivalent to For y = 1 to 9 in VB. But then java threw me for a loop (so to speak) when it then does a parseInt() on the y - which I was thinking was numeric. Finally, that comparison in the else if statement really got me, because that makes it look like ou're comparing integers to strings for equality. The problem with that in VB would be that the string would, in some cases have a leading zero and the integer, even when converted to a string, would not have the leading zero. As I said, I failed this one. So unless a someone proficient in Java explains what's going on in the algorithm to us, or someone proficient in both Java and VB comes along, it's a tough nut to crack. "Kamila" wrote: Hello, I am sending again my question or problem to you. i am not able to do so. please help me. I have one columen witch contain Ids 11 sifer in excel. i have one formula in javascript to validate id number. i have 3500 id numbers and i can not incert every number to check it is write or wrong. is there any way to find out or convert that javascript function into excel and get all wrong and right ids in next columen? Right IDs, 01014927367 01015726136 Wrong Ids 15010933773 45290122333 I got this function. <html <head <titlechecking ID number formula </title </head <body <form <INPUT NAME="ID" SIZE=11 maxlength=11 <INPUT TYPE="button" CLASS="button" NAME="bname" VALUE="ID Number" onClick="(ValidateID(this.form.ID.value))"<br </form <script type="text/jscript" function ValidateID(ID) { var TmpStr var Tmpr var vekt1 = "376189452" var vekt2 = "5432765432" var tot = 0 var base = 11 var value var vekt var TmpStr2 var Tmpr2 var vekt2 = "5432765432" var tot2= 0 var base = 11 // for first number for (i=0;i<9 ;i++) { value = ID.substr(i,1) vekt = vekt1.substr(i,1) tot = tot + (parseInt(value) * parseInt(vekt)) } i=tot/11 i=tot-(parseInt(i)*11) i=11-i TmpStr = ID TmpStr = ID.substr(9,1) Tmpr=(ID.length) // for second number for (y = 0; y < 10 ; y++) { value = ID.substr(y,1) vekt = vekt2.substr(y,1) tot2 = tot2 + (parseInt(value) * parseInt(vekt)) } y=tot2/11 y=tot2-(parseInt(y)*11) y=11-y TmpStr2 = ID TmpStr2 = ID.substr(10,1) Tmpr2=(ID.length) // integer check if (base!=Tmpr) { document.write("You must enter 11 number for ID (0 - 9) ") } else if ( parseInt(i)==TmpStr && parseInt(y)==TmpStr2) { document.write(ID +" " + "is right ID") } else { document.write("Obs!! You write wrong ID number!") } } </script </body </html best regards, -- Kamila |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
A challenge: referencing cell contents in nested function formula | Excel Worksheet Functions | |||
Javascript Question | New Users to Excel | |||
Converting a row or column in excel to a Javascript array | Excel Discussion (Misc queries) | |||
MATCH FUNCTION?...challenge | Excel Worksheet Functions | |||
A COUNTIF function Challenge | Excel Worksheet Functions |