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

Does this formula do what you want?

=MOD(SUMPRODUCT(SEARCH(MID(A1,ROW(1:99),1),"012345 6789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-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),"012 3456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-99,43),1)

--
Rick (MVP - Excel)


"CLI-Art" wrote in message
...
Is there a mod43 check digit calculator in Excel. If not, has anyone out
there made one?

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),"012345 6789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-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),"012 3456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-99,43),1)

--
Rick (MVP - Excel)


"CLI-Art" wrote in message
...
Is there a mod43 check digit calculator in Excel. If not, has anyone out
there made one?

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),"0123 456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.$/+%")
)-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),"0123 456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.$/+%")
)-99,43)

which you might place in, say, F3.

--
Rick (MVP - Excel)


"CLI-Art" wrote in message
...
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),"012345 6789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-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),"012 3456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-99,43),1)

--
Rick (MVP - Excel)


"CLI-Art" wrote in message
...
Is there a mod43 check digit calculator in Excel. If not, has anyone
out
there made one?

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),"0123 456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.$/+%")
)-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),"0123 456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.$/+%")
)-99,43)

which you might place in, say, F3.

--
Rick (MVP - Excel)


"CLI-Art" wrote in message
...
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),"012345 6789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-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),"012 3456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-99,43),1)

--
Rick (MVP - Excel)


"CLI-Art" wrote in message
...
Is there a mod43 check digit calculator in Excel. If not, has anyone
out
there made one?

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" wrote in message
...
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),"0123 456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.$/+%")
)-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),"0123 456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.$/+%")
)-99,43)

which you might place in, say, F3.

--
Rick (MVP - Excel)


"CLI-Art" wrote in message
...
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),"012345 6789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-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),"012 3456789ABCDEFGHIJKLMNOPQRSTUVWXYZ-.
$/+%") )-99,43),1)

--
Rick (MVP - Excel)


"CLI-Art" wrote in message
...
Is there a mod43 check digit calculator in Excel. If not, has
anyone
out
there made one?

Here is a UDF. Add it to a standard code module and use formula in cell.
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

--

Regards,
Nigel




"CLI-Art" wrote in message
...
Is there a mod43 check digit calculator in Excel. If not, has anyone out
there made one?

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

"Nigel" wrote:

Here is a UDF. Add it to a standard code module and use formula in cell.
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

--

Regards,
Nigel




"CLI-Art" wrote in message
...
Is there a mod43 check digit calculator in Excel. If not, has anyone out
there made one?

UDF = user defined function, that can be used just like standard Excel
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




"CLI-Art" wrote in message
...
I am a newer user to excel and this post has me lost. Can you "dumb" it up
a
bit?

"Nigel" wrote:

Here is a UDF. Add it to a standard code module and use formula in cell.
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

--

Regards,
Nigel




"CLI-Art" wrote in message
...
Is there a mod43 check digit calculator in Excel. If not, has anyone
out
there made one?

there is an example at this site.

http://www.code39barcodes.com/code-39-check-digit.html

--
Regards,

OssieMac


"CLI-Art" wrote:

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

Creating a Mod 43 Check Digit Calculator in Excel

1. Enter the number you want to calculate the Mod 43 Check Digit for in a cell.

2. In the cell next to the number, enter the following formula:

1. Break down the number into individual digits using the MID function.
2. Multiply each digit by a corresponding weight from the Mod 43 Check Digit algorithm.
3. Sum the weighted digits using the SUMPRODUCT function.
4. Subtract the result from 43 and take the remainder using the MOD function.
5. Subtract the result from 43 again to get the Mod 43 Check Digit.

3. Press enter and the Mod 43 Check Digit for the number will be displayed in the cell.