Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
Is there a mod43 check digit calculator in Excel. If not, has anyone out
there made one? 
#2




Answer: Mod 43 Check Digit calculator
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: Formula:
3. Press enter and the Mod 43 Check Digit for the number will be displayed in the cell.
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
there is an example at this site.
http://www.code39barcodes.com/code39checkdigit.html  Regards, OssieMac "CLIArt" wrote: Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#4
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
Just a little extra. If you Google mod 43 check digit then you will find a
myriad of them. The one at the wikipedia site looks simpler. http://en.wikipedia.org/wiki/Code_39  Regards, OssieMac "OssieMac" wrote: there is an example at this site. http://www.code39barcodes.com/code39checkdigit.html  Regards, OssieMac "CLIArt" wrote: Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#5
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#6
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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 "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#7
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#8
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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 "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#9
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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) "CLIArt" 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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#10
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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) "CLIArt" 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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#11
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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 wordwrap 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) "CLIArt" 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) "CLIArt" 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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#12
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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 wordwrap 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) "CLIArt" 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) "CLIArt" 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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#13
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
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. "CLIArt" 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 wordwrap 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) "CLIArt" 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) "CLIArt" 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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#14
Posted to microsoft.public.excel.programming




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 16digit 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) "CLIArt" wrote in message ... 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. "CLIArt" 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 wordwrap 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) "CLIArt" 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) "CLIArt" 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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#15
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
I think that you should input your numbers as strings. Either format the
column as text or precede the number with a single quote.  Regards, OssieMac "CLIArt" wrote: 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. "CLIArt" 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 wordwrap 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) "CLIArt" 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) "CLIArt" 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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#16
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
UDF = user defined function, that can be used just like standard Excel
functions. To install this code Press AltF11 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 AltQ 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 doublequotes, 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 "CLIArt" 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 "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
#17
Posted to microsoft.public.excel.programming




Mod 43 Check Digit calculator
Again, thank you very much. Now that I figured a work around for the 16
digit problem I am off to the races. Your help is greatly appreciated!! "Rick Rothstein" wrote: 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 16digit 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) "CLIArt" wrote in message ... 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. "CLIArt" 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 wordwrap 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) "CLIArt" 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) "CLIArt" 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) "CLIArt" wrote in message ... Is there a mod43 check digit calculator in Excel. If not, has anyone out there made one? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
GTIN12 Check digit calculator  Excel Discussion (Misc queries)  
GTIN12 Check digit calculator  Excel Discussion (Misc queries)  
Mod10 Check Digit  Excel Worksheet Functions  
Check last digit in cell  Excel Programming  
Mod10 Check Digit  Excel Worksheet Functions 