Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number
Hello,
I have the following Serial No check Digit 40212463794 4 40212463802 2 40212463810 0 40212463828 8 40212463836 6 40212463844 4 40212463851 1 The last number is a check digit and the check digit is arrived by =Number-10*INT(Number/10) The Number is including the check digit. Now I want to get the number without check digit and check digit in another column so if I input the serial numbers like: 4021246379 4021246380 4021246381 4021246382 4021246383 4021246384 4021246385 then how can i get the check digit? Is there an easy formula? Thanks Abdul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number
You need to convert the number to a fixed lenght string and then get the
right character. in excel =RIGHT(TEXT(A1,"###########"),1) if you want the number instead of a character =value(RIGHT(TEXT(A1,"###########"),1)) in VBA mydigit = right(format(A1,"###########"),1) if you want the number instead of a character mydigit = value(right(format(A1,"###########"),1)) "Abdul" wrote: Hello, I have the following Serial No check Digit 40212463794 4 40212463802 2 40212463810 0 40212463828 8 40212463836 6 40212463844 4 40212463851 1 The last number is a check digit and the check digit is arrived by =Number-10*INT(Number/10) The Number is including the check digit. Now I want to get the number without check digit and check digit in another column so if I input the serial numbers like: 4021246379 4021246380 4021246381 4021246382 4021246383 4021246384 4021246385 then how can i get the check digit? Is there an easy formula? Thanks Abdul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number
Thanks,
I think I did not explain my question well. If I know the whole number then it is very easy. I know only the following serial numbers: 4021246379 4021246380 4021246381 4021246382 4021246383 4021246384 4021246385 What i want to get is the following Serial No 40212463794 40212463802 40212463810 40212463828 40212463836 40212463844 40212463851 The last digit which added with the number is a check digit and the check digit is arrived by =Number-10*INT(Number/10) My problem is that the number in the above formula is the number including the check digit. And that check digit is unknown for me. What I thought is that there would be a mathematical equation to find out that part then I could put it as the last digit in my number my number. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number
Hello,
=A1*10+A1-10*INT(A1/10) should do it. Regards, Bernd |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number
Sorry, thats wrong (your formula derives the digit, it does not
produce it). We need to derive or to know the checksum algorithm. Regards, Bernd |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number
You would have to talk to the person that wrote the algorithm to
calculate the check digit when they printed out the serial numbers in the first place (production line process, etc.). Normally, a check digit is included on serial numbers and sometimes on part numbers also, to prevent (or catch) incorrect data entry. In other words, if a customer calls in for tech support, and accidentally transposes 2 digits while reading the number over the phone, then without the check digit, how would anyone know that an error has been made? This check digit process is to prevent fraud, wrong spare parts from being dispatched, and a whole range of errors, etc. It is not really safe to be re-generating the check digit, if it has not been included in the data you are getting. How will you know that a digit is wrong or transposed? -- Regards, Bill Renaud |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula to get number
Hello!,
Thanks for all your support. Let me explain it further. I have the whole number on the product and the number includes the last digit which is a check digit. I want to scan each and every item in to the system, which is more than a million. Instead of doing this if I make the serial numbers which is very easy as you know and i can get the check digit using a formula and then CONCATENATE both numbers I could import this data in to the system. When I tested the numbers using =Number-10*INT(Number/10) (number in the above formula is the number including the check digit) I get the check digit. Thanks, Abdul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to automatically change one number to another number | Excel Discussion (Misc queries) | |||
formula to throw out high number and low number | Excel Worksheet Functions | |||
Creating number formula to count number occurences in a data set | Excel Programming | |||
Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
countif formula to find the occurances of a number that is greater than one number but less than another | Excel Discussion (Misc queries) |