C

#### CLI-Art

Is there a mod43 check digit calculator in Excel. If not, has anyone out

there made one?

there made one?

C

Is there a mod43 check digit calculator in Excel. If not, has anyone out

there made one?

there made one?

Ad

O

O

myriad of them. The one at the wikipedia site looks simpler.

http://en.wikipedia.org/wiki/Code_39

R

=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)

N

e.g.

=ModFT("123") as a direct conversion of some text or

=ModFT(A3) as a range reference

Const charSet As String = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%"

Function ModFT(sValue As String)

Dim i As Integer, T As Long

For i = 1 To Len(Trim(UCase(sValue)))

T = InStr(charSet, Mid(sValue, i, 1)) - 1 + T

Next i

ModFT = sValue & Mid$(charSet, (T Mod 43 + 1), 1)

End Function

C

be working correctly. Should I just copy the entire string into the first

cell and go from there?

Ad

C

I am a newer user to excel and this post has me lost. Can you "dumb" it up a

bit?

bit?

R

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.

C

numbers 10 and up with the corresponding alpha characters.

R

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).

C

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

Ad

C

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.

R

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.

O

column as text or precede the number with a single quote.

N

functions.

To install this code

Press Alt-F11 and the VBA editor will open. From the menu choose Insert,

then from the drop down click Module.

Copy then paste all of the code shown below.

Close the editor by pressing Alt-Q

To use the UDF, you type the function name e.g

=ModFT

as you would any Excel function, then open bracket

=ModFT(

Then either type the string you are trying to convert by putting in inside

double-quotes, then close brackets

=ModFT("MYSTRING")

or reference another cell with the string you wish to convert, so lets

assume your string is in A1, you can type in B1 the function and reference

to A1

=ModFT(A1)

Every time you change the value in A1 the converted value appears in B1 (in

this example)

' copy from line below to end of copy

Const charSet As String = _

"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-. $/+%"

Function ModFT(sValue As String)

Dim i As Integer, T As Long

For i = 1 To Len(Trim(UCase(sValue)))

T = InStr(charSet, Mid(sValue, i, 1)) - 1 + T

Next i

ModFT = sValue & Mid$(charSet, (T Mod 43 + 1), 1)

End Function

' end of copy

--

Regards,

Nigel

(e-mail address removed)

C

digit problem I am off to the races. Your help is greatly appreciated!!

Ad

Lets say i have numbers 1 to 10 in my spreadsheet and i want to add modulo check digit to them all at once. Whats the formula to automate them unlike going to change the column number in the formula for every number i want to assign the check digit?

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.

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.