Formula to get number

[Abdul]

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

 

[Joel]

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]

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

 

[Bernd P]

Hello,

=A1*10+A1-10*INT(A1/10)

should do it.

Regards,
Bernd

 

[Bernd P]

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

 

[Bill Renaud]

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?

 

[Abdul]

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