Re: Mod 43 Check Digit calculator

Yes, I see what you mean about being one off. This should fix that problem...

=MID("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%",
MOD(SUMPRODUCT(SEARCH(MID(A1,ROW($1:$99),1),
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%") )-98,43),1)

As for you 16-digit problem... that is Excel's fault... it can only handle numbers with 15 significant digits. If you are not using this number for calculating with, then you can either enter the numbers with a leading apostrophe (which make the entry a text entry). The other possible way to solve the problem is to format the column as Text so that Excel won't think you are entering a number. Either way, you won't need to use a leading 1. Let us know how that works out for you.

--
Rick (MVP - Excel)


"CLI-Art" <CLIArt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C17069A1-7EA7-4969-8751-480F731BDB1A@xxxxxxxxxxxxxxxx
I have to take back the working part. The digit seems to be off by 1 even
taking the
missing leading 1 into consideration. for 900001 ( leaving out all of the
leading #s) I get a check digit of 9 and it should be 10 which would be an A.



"CLI-Art" wrote:

This one seems to be working. Thank you very much for your help. I am
having an additional issue which is somewhat unrelated, but causing a
miscalculation of the check digit. The number that I am adding a mod 43
check digit to is a 16 digit number and in order to get it to serialize in
Excel I had to force the initial 1 in with cell formatting. the first number
is 1000000009000001. If I enter that number as I have typed it here the end
1 changes to a 0 and it does not increment correctly. I have tried numeous
cell formats. Can I modify the formula for this item only and force in the
extra 1

"Rick Rothstein" wrote:

> Which formula are you using... the second one I posted (in my original
> response to you) should return the check character itself rather than > the
> check number. Here is that formula with the absolute reference problem > that
> I mentioned in my other post fixed...
>
> =MID("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%",
> MOD(SUMPRODUCT(SEARCH(MID(A1,ROW($1:$99),1),
> "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%") )-99,43),1)
>
> As before, there is a single reference to A1 that needs to be changed > to the
> address for the first cell containing your data. I just noticed there > is a
> space in the encoding string at the 39th position. That means if your > check
> number is 39, then the above formula will return a space which will be > "hard
> to see". I have also manually broken the formula up in order to stop > your
> newsreader from using the space character as a point to word-wrap at > (which
> makes it possible for you, and others reading this thread, to > accidentally
> erase it when combining the broken lines into a single line formula > when
> placing it into the Formula Bar).
>
> -- > Rick (MVP - Excel)
>
>
> "CLI-Art" <CLIArt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A69D7148-79D3-47F2-9620-DC98E0A0C2D2@xxxxxxxxxxxxxxxx
> > Almost there. The formula seems to be working EXCEPT, it is not > > replacing
> > numbers 10 and up with the corresponding alpha characters.
> >
> > "Rick Rothstein" wrote:
> >
> >> First off, since you will probably want to copy the formula down the
> >> column,
> >> I left off a couple of needed absolute references. Use this formula
> >> instead
> >> of the one I posted originally...
> >>
> >> =MOD(SUMPRODUCT(SEARCH(MID(A1,ROW($1:$99),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.$/+%")
> >> )-99,43)
> >>
> >> My formula assumes your text is in A1... you would put the above > >> formula
> >> in
> >> any other column (in most likely in row 1 of that column to keep the > >> text
> >> and the MOD43 number in sync). If you have more text under A1 that > >> you
> >> want
> >> the MOD43 number for, then just copy my formula down through those > >> rows.
> >> If
> >> your text strings are in a different column, then change the A1 > >> reference
> >> in
> >> my formula to the first cell in the column the text is in. For > >> example,
> >> if
> >> your first text string is in E3, then the above formula would become
> >> this...
> >>
> >> =MOD(SUMPRODUCT(SEARCH(MID(F3,ROW($1:$99),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.$/+%")
> >> )-99,43)
> >>
> >> which you might place in, say, F3.
> >>
> >> -- > >> Rick (MVP - Excel)
> >>
> >>
> >> "CLI-Art" <CLIArt@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:F483F24C-3F81-4F57-900A-377FED9AA3ED@xxxxxxxxxxxxxxxx
> >> > Perhaps I am not gettting this into my file correctly but it does > >> > not
> >> > seem
> >> > to
> >> > be working correctly. Should I just copy the entire string into > >> > the
> >> > first
> >> > cell and go from there?
> >> >
> >> > "Rick Rothstein" wrote:
> >> >
> >> >> Does this formula do what you want?
> >> >>
> >> >> =MOD(SUMPRODUCT(SEARCH(MID(A1,ROW(1:99),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
> >> >> $/+%") )-99,43)
> >> >>
> >> >> I think it calculates the MOD43 check number for the text in A1. > >> >> If
> >> >> you
> >> >> need
> >> >> the formula to calculate the character equivalent to this number, > >> >> then
> >> >> try
> >> >> this formula...
> >> >>
> >> >> =MID("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
> >> >> $/+%",MOD(SUMPRODUCT(SEARCH(MID(A1,ROW(1:99),1),"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
> >> >> $/+%") )-99,43),1)
> >> >>
> >> >> -- > >> >> Rick (MVP - Excel)
> >> >>
> >> >>
> >> >> "CLI-Art" <CLI-Art@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> >> news:4DE805E4-A909-4E54-ACD4-3BB39CACF436@xxxxxxxxxxxxxxxx
> >> >> > Is there a mod43 check digit calculator in Excel. If not, has
> >> >> > anyone
> >> >> > out
> >> >> > there made one?
> >> >>
> >> >>
> >>
> >>
>
>

.

Relevant Pages

  • Re: strings patterns and the like
    ... >>You could take the string and have a loop to look at each character in the ... Then you could have an array of size 6 which checks the current ... >>position to see if it's an x or a digit... ...
    (comp.lang.java.help)
  • Re: passing array to isdigit()
    ... > in computer programming symbol has a different meaning; ... >>whole string and checks every character ... >>Its interface provides the means to check whether the last digit ...
    (comp.lang.c)
  • Re: INFINITY Revisited
    ... > What is the difference between a digit string and a character sting? ... nonterminating decimal fraction is that it, uh, has no end (which is ... Seaman said "those are character strings", but of course so are the ... The fact that one can string symbols together is no ...
    (sci.math)
  • Re: How do I - Compile a number into the program
    ... and use a second program to insert that number into the ... add a long string of Chrin a module somewhere. ... replace the first character with your ... first digit, and take the difference of that digit and 127, and add that ...
    (microsoft.public.vb.general.discussion)
  • Re: How do I - Compile a number into the program
    ... and use a second program to insert that number into the ... > add a long string of Chrin a module somewhere. ... replace the first character with your ... > first digit, and take the difference of that digit and 127, and add that ...
    (microsoft.public.vb.general.discussion)
Loading