Calculating a Check Digit Routine

[rojobrown]

I have the following formula that is used to calculate a MOD 10 check digit
routine. For example: If I entered (8 digits) 45678912 in cell A1, the
formula would return a 2 as the 9th digit.

This is the formula that I am using but did not write so I'm having trouble
following it.
=10-MOD(SUM(--MID(TEXT($A1,"000000000"),{2,4,6,8},1),--RIGHT(MID(TEXT($A1,"000000000"),{1,3,5,7,9},1)*2),--LEFT(TEXT((MID(TEXT($A1,"000000000"),{1,3,5,7,9},1)*2),"00"))),10)

I need to modify this formula so that it will look at 15 digits and
calculate the 16th. I've tried multiple things but have had no luck. Any
suggestions?

 

[T. Valko]

See this:

http://www.google.com/search?hl=en&source=hp&q=excel+mod+10+check+digit&aq=f&oq=&aqi=

 

[barry houdini]

See this:

http://www.google.com/search?hl=en&source=hp&q=excel+mod+10+check+dig...

--
Biff
Microsoft Excel MVP








- Show quoted text -

Your formula will return 10 in some instances, presumably that should
be zero. This formula should return the same results as yours (that
one anomaly apart) for 8 digit numbers....and also works for any
number of digits. Try for 15 and see if it works as you expect.......

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD
(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00"),{1,2},1)),10)

or specifically for 15 digits....

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},
1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)),10)

regards, barry

 

[T. Valko]

I was wondering what ever happened to "daddylonglegs" that used to post
those amazing date formulas!

--
Biff
Microsoft Excel MVP

See this:

http://www.google.com/search?hl=en&source=hp&q=excel+mod+10+check+dig...

--
Biff
Microsoft Excel MVP








- Show quoted text -

Your formula will return 10 in some instances, presumably that should
be zero. This formula should return the same results as yours (that
one anomaly apart) for 8 digit numbers....and also works for any
number of digits. Try for 15 and see if it works as you expect.......

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*(MOD
(ROW(INDIRECT("1:"&LEN(A1)))+LEN(A1)+1,2)+1),"00"),{1,2},1)),10)

or specifically for 15 digits....

=MOD(SUMPRODUCT(-MID(TEXT(MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15},
1)*{2;1;2;1;2;1;2;1;2;1;2;1;2;1;2},"00"),{1,2},1)),10)

regards, barry

 

[barry houdini]

I was wondering what ever happened to "daddylonglegs" that used to post
those amazing date formulas!

Hello Biff,

daddylonglegs was my excelforum "alter ego" - he's still going strong
over there - but I'm gonna be barry from now on everywhere else....
(except perhaps the email address)..it's too much trouble keeping BOTH
egos in check....

regards, barry