Free Microsoft Excel 2013 Quick Reference

Digital parity check

How would you write a function in VBA to check whether the digits of an input number are either all odd or all even? If the digits are all the same parity the output should equal the input number otherwise the output is null. As a novice I am struggling and hope you can help.


Post your answer or comment

comments powered by Disqus
I have seen a few formulas online to covert an isbn 10 to isbn 13, like this one (http://ndpsoftware.com/isbn.php) but i was wondering if there is a macro for reverse, i.e. conversion from isbn 13 to 10, using MOD 11 the algorithm is:

Converting from Bookland EAN-13 to a 10-digit ISBN
978-0-393-04002-9
1. Strip the check-digit, as a new modulus 11 check-digit will be generated for the 10-digit ISBN. This gives us 978-0-393-04002-

2. Strip the “978” EAN Bookland prefix, giving a 10-digit ISBN string without its check-digit. ( Stripping the “978” prefix gives us 0-393-04002-

3. The 10th digit of the 10-digit ISBN is the modulo 11 check-digit, which will have a value of 0-10, with the value 10 being represented by “X”.
Modulus 11 algorithm to calculate check digit for the 10-digit ISBN (check digit unknown)
Incomplete 10-digit ISBN = 0-393-04002-?

weightage : (0*10 + 3*9 + 9*8 + 3*7 + 0*6 + 4*5 + 0*4 + 0*3 + 2*2) ie ( 10 * first digit, 9*second & so on till 2* 9th digit)

Check digit = mod11 (11 - mod11 (Product Total)) = mod11 (11 – mod11 (144)) = 10

(Special case note: When the check digit calculates to 10, it is represented as an “X”.)
(Technical note: mod11 of a test number returns the remainder of the test number divided by 11 unless the number is less than 11 in which case it returns the test number itself)
Complete 10-digit ISBN = 0-393-04002-X

would appreciate if some one can help with this or let me know if such formula already exsists. Thanks

I would LOVE some help creating a POSTNET function or VB MACRO.

As you may know, the POSTNET format has to be:

zip+4 and drop point and a check digit.
The check digit is:

1. the digits of the zip+4 and the drop point added together
2. 10 minus the MOD of that sum divided by 10 and if the value is 10 the check digit is zero.
3. That final sum can be verified by taking the MOD of the sum plus the check digit divided by 10 which must equal 0.

I accomplished this over several columns. My zip code are stored as xxxxx-xxxx.

The first column adds the zips digits where A2 has the zip+4 and the drop point with a series of IF statements because the Drop Point has a variable string length:

(It would also be nice to loop through the exact string legnth of the drop point value instead of that series of if
statements)

The next column calculates the check digit and returns zero if the calculation produces 10:

The next column verifies the result:

The last column outputs the data (strips the dash and concatenates the string) and outputs an empty string if the verify
field is not 0:

Any help making this into a single function to act on a zip column and a drop point column or make it into a macro would be
GREATLY appreciated.

Steven

I'm working on a project where I need to prevent the user from entering non-digit string inputs. I've tried the using

	VB:
	
IsNumeric(MyString) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but that doesn't prevent , + - signs to be entered. I also had to conditionally enable the input of a string with leading zeros like "01" so what I've ended up with is a rather messy looking' sub that handles this (don't know if forum rules allows me to show it here?). The problem is that I'm not a 100% sure that my sub fit to handle all eventualities so therefore my question is whether there is an easier, fool proof way to check my strings?

I would like a function that checks an input number to see if it contains unique digits. If the digits are unique the output is 1 else the output is -1. Thus, supposing we call the function UNIQ(), we find UNIQ(15423) = 1 but UNIQ(154532) = -1. The input is always a positive integer. Please advise.

Hi,
I have some code where I need to check if the first five char are numbers and not letters.

I have in a column for example
12345-someone is here
23456-someone else is here
someone is here too

I need to get all of the ones that have 5 digits and not pull in the other into a new list.

I have tried Left(CPHierAll.Cells(CPHierAllRow, 1),5) which will get me the fist 5 char. and then i need to check to make sure that they are all numbers and not char.

if Left(CPHierAll.Cells(CPHierAllRow, 1),5) = "#####" then
But this does not bring anything in.

I also tried if CPHierAll.Cells(CPHierAllRow, 1),5) = "#####" then

I have 3 column that i am checking for different thinks the first two work just find and seperate out on the check but the last one with the numbers is being a pain.

can someone help me out.

In range A1:A10 there are different values in cells, some only digits, some digits with characters. Only entries with 10 digits are correct ones! All digits 0123456789 apply.

I need a VBA code that will check each cell in range for exactly 10 digits. If there are entries that do not match this condition, msgbox should appear -saying:
- either in which rows there are wrong values
- either exactly in which cells there are wrong entries or
- preferable Excel directly selects (without any msgbox) the first cell with wrong input, so that user can quickly correct it. After user changes content of a cell and hits ENTER key, macro runs again from the start checking next wrong entry. This sounds as combination of sub and private sub routine.

Any help greatly apreciated!

I have the following algorithm which is used to calculate a check digit for a value. How do I go about putting this into an excel sheet so that it looks at a given cell and gives me the corresponding check digit for the value in that cell??? For example the value 00000001 = G, 00000002 = M, 00000003 = S etc... All possible check digit values are A - Z characters only based on the algorithm.

I don't know too much about this, I am guessing the below algorithm is in some sort of VB format?

weight = 7
check = 0

for each number in string starting from beginning
{
if weight < 2 then
weight = 7

check = check + (weight *number)
weight = weight -1
}

check = check mod 26
map check to character from A to Z where 0 becomes A and 25 becomes Z

I need to add code to the following that will color cells if the cell does not have a 2 digit number. Cells are formatted as text. I have data validation set for when values are keyed. But sometimes values are pasted and it might be a 1 digit number.

Right now this code checks for blanks, if found it adds a "00". I need it then to recheck all cells for a 2 digit format.

Code:
Set rng2 = Range(Cells(6, 7), Cells(lastrow, 7))
For Each cc In rng2
If cc.Value = "" Then cc.Value = "00"
Next cc
I tried this but It didn't work properly??
Code:
Set rng2 = Range(Cells(6, 7), Cells(lastrow, 7))
For Each cc In rng2
If cc.Value = "" Then cc.Value = "00"
If Format(Val(Text), "00")  Text Then
SysFound = True
        Range(Cells(cc.Row, 2), Cells(cc.Row, 23)).Interior.ColorIndex = 33
    Else
        Range(Cells(cc.Row, 2), Cells(cc.Row, 23)).Interior.ColorIndex = 36
    End If
Next cc
If SysFound Then MsgBox "See Blue Rows"


I am working on checking a cell in a form which must be 4 digit number in text format. Anyone has done this before?

Thanks,

Meimei

Hi All,

My column P stores the revenue code, and the code should be 13 digits.

I want to check whether each line of code is 13 digits, and give a warning message if not.

Please help !

Thank you !

Hello everyone,

I am new at the forum, and have a tricky question.

My company uses UPC codes for identifying its products. As you might know, the UPC codes have 11 digits + 1 digit check code. And the formulation to calculate this check digit is as following:

Step One: From the right to the left, start with odd position, assign the odd/even position to each digit.

Step Two: Sum all digits in odd position and multiply the result by 3. (7+6+1+1+5+2)*3=66
Step Three: Sum all digits in even position. (2+4+2+7+4)=19
Step Four: Sum the results of step three and four: 66+19=85
Step Five: Divide the result of step four by 10. The check digit is the number which adds the remainder to 10. In our case, divide 85 by 10 we get the remainder 5. The check digit then is the result of 10-5=5.

So, what I did to calculate this was like that:
1. Cell A1 is the eleven digit number: 72641217542
2. Cells A2 to A12 are 7, 2, 6, 4, 1, 2, 1, 7 ,5, 4, 2 correspondingly.
3. Cell A13 has the following formula to calculate the check digit:

=RIGHT(ROUNDDOWN((10-((((((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11))/10)-ROUNDDOWN((((A12+A10+A8+A6+A4+A2)*3)+(A3+A5+A7+A9+A11))/10,0))*10)),0),1)

4. Cell A14 has the following formula to combine whole:

=CONCATENATE(A1,A13)

I know it is complicated but it works perfectly. Could you guys advice me any other shorter ways to do that?

Thanks in advance,

Ray

I have an interesting problem where I need to calculate a check digit from a string. The check digit is calculated on the ascii value of each character in the string added together, taking the sum and using the last two digits. I can get the last two digits using the = right command and can calculate the ascii value of a single character using the =code function, the problem I am running into is I need to get that value for each number in the string of 58 characters and want to do it with a single formula. An example is the string
7084260190000002049/00000011/00000000/000000/0000000/00/00/79, the last two digits are the check sum of the ascii value of the first 59 characters in the string, using only the last 2 characters of the string
Any help would be appreciated

I have a formula that gives you the check digit for your UPC but I have to brake the UPC Code down to one number per cell I would like to change the formula so I don't have to do this. The Formula is

MOD(10-(MOD(((j2+l2+n2+q2+s2+u2)*3)+(k2+m2+o2+r2+t2),10)),10)

UPC Code: 8002810001 Check Digit:6

Hey guys, i am looking for a Mod 11 excel formula for valid check digit(for isbn 10)

example:
The check digit is the last digit of an ISBN. It is calculated on a modulus 11 with weights 10-2, using X in lieu of 10 where ten would occur as a check digit.

This means that each of the first nine digits of the ISBN – excluding the check digit itself – is multiplied by a number ranging from 10 to 2 and that the resulting sum of the products, plus the check digit, must be divisible by 11 without a remainder.
For example ISBN 0843610727

ISBN No. : 0 8 4 3 6 1 0 7 2 7
Weight : 10 9 8 7 6 5 4 3 2

i.e.(0*10 + 8*9 +4 *8 + 3*7 + 6*6 + 1*5 + 0*4 + 7*3 + 2*2 + 7)
--------------------------------------------------------------------------------

Products 0 +72 +32 +21 +36 +5 +0 +21 +4 +7

Total: 198

As 198 can be divided by 11 without remainder 0-8436-1072-7 is a valid ISBN.
7 is the valid check digit.

will really appreciate if some one can help me with and excel formula for the same, thanks guys..[/list]

Please help!

I'm trying to create a check digit using an existing string of 20 numbers.
The calculation of this digit must follow this method
1. All even numbers added together
2. All odd numbers added together and multiplied by 3
3. These two results need to be added together
4. Take the last digit of the result in 3 and subtract 10 (The result needs
to be a single check digit between 0 & 9)

EG. 01248120470000003001, results in a check digit of 9

There may be up to a million of these strings of numbers!

My question is can this task be performed using one equation? I have split
it up into sections to keep it simple for now, but I am stuck on section 4.

Any help much appreciated...many thanks in advance

Ilan

I found this great formula (below) for calculating the 12th (check
digit) for a 12 digit upc code and then yielding the entire code
including 12th digit. Does anybody know how to modify or alter this
formula to calculate the 13th digit (check digit) for a 13 digit UPC A
code and yield the entire code including the 13th digit?

=A1&MOD(10-MOD(SUMPRODUCT(MID(A1,ROW($1:$11),1)*
(1+2*MOD(ROW($1:$11),2))),10),10)

The long way is:
Stating as example the following code: 739007812345

Start by adding every other figure from the right and then multiply by
3.
5 + 3 + 1 + 7 + 0 + 3 = 19 * 3 = 57
After this add the rest of the figures:
4 + 2 + 8 + 0 + 9 + 7 = 30
Add the above results 57 + 30 = 87
Next reduce this amout 87 from the closest higher ten (90) and you
receive 90 - 87 = 3
3 is the check digit.

Thanks,

M. Jack

Ok guys..Any help would be great.. What i'm trying to accomplish here is. Creating a function and/or formula in Excell or access that will calculate the check digit value of a 20 digit number. I have seen several sites that have VB code on how to do..however I do not know how to incorporate vb code into a Ms Access db and make it do my requested task...

ie... http://www.makebarcode.com/info/appnote/app_003.html
http://www.excelbanter.com/q-t_19172...eck-Digit.html
http://www.freevbcode.com/ShowCode.Asp?ID=1035

ie... I have a field "barcodewithnocheckdigit" and an example value for that field is "8260212616630008000" The check digit sum for that figure is 9. I have 40,000 records. Is it possible to have this code/query calculate and update the check digit value field?

The instructions on calculating a check digit value are here :

http://www.uc-council.org/ean_ucc_sy.../cdc.html#SSCC

So I guess my question should be "how do I use this code in my database" Please keep in mind that I have no programming knowledge whatsoever...therefore I need step by step information pretty much..

Thank in advance for your help.

Hi everyone

I've googled without any luck!

I want to use a function to test that the user has entered a "correct"
ISBN by validating the check digit.

I want to use
=if(right(a1)(11-MOD(SUMPRODUCT(MID(A1,ROW(INDIRECT("1:9")),1)*{10; 9;8;7;6;5;4;3;2}),11)),"Invalid","")
but every possible last digit give the result "invalid".

To complicate matters further in some cases the result of the original
function could be 11 which is transalted into "X" on a book. How would
I need to change my formula to cope with that?

Thanks

Colin

--
Colin Vicary
------------------------------------------------------------------------
Colin Vicary's Profile: http://www.excelforum.com/member.php...o&userid=10472
View this thread: http://www.excelforum.com/showthread...hreadid=395037

Does anyone know how to generate a unique card number based on a
previously existing number? I'm trying to create a 16 digit Check
number by only given the first 15 digits. I've been to this site:
http://www.beachnet.com/~hstiles/cardtype.html to use the Excel spread
sheet file and it doesn't give me the info I need. Any help is
welcomed.

Thanks,
John

--
Union70

I want to calculate the check digit for 11 digit barcode in an Access 2007 query.
Excel uses MOD, but I don't know how to recreate this in Access.
IF(MOD(3*(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1))+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1),10)=0,0,10-MOD(3*(MID(A1,1,1)+MID(A1,3,1)+MID(A1,5,1)+MID(A1,7,1)+MID(A1,9,1)+MID(A1,11,1))+MID(A1,2,1)+MID(A1,4,1)+MID(A1,6,1)+MID(A1,8,1)+MID(A1,10,1),10))
thanks,

Hi all
I have got 2 columns and 17 digits for each column and i want to check digit between 2 columns and then report different digit.m

Hope you help and Thank for help!

example

A | B | result digit differance
PE2ET77141SB02023 | PE2FT77141SB02023 | 5
MNBBS2D305W485838 | MNBBB2D305W485838 | 5
MNBBS2D304W375808 | MNBBS2D304W37588 | 16

Hi all,

I wanted to combine these if possible,

which produces an 11 digit UPC, or if UPC = 12 digits, it leaves it be.

and this code

which just calculates the check digit.

I am assuming an IF statement is needed, but I can't seem to wrap my head around it right now.

But I also ran across some code, but don't know how to reference it within the worksheet.

'(None)

Public Function Mod10CheckDigit(Barcode As String) As Integer
    Dim i As Integer
    Dim TotalOdd As Integer
    Dim TotalEven As Integer
    Dim Total As Integer
    Barcode = Trim(Barcode)
    'get odd numbers
    For i = 1 To Len(Barcode) Step 2
        TotalOdd = TotalOdd + CInt(Mid(Barcode, i, 1))
    Next i
    TotalOdd = TotalOdd * 3

    'get even numbers
    i = 0
    For i = 2 To Len(Barcode) Step 2
        TotalEven = TotalEven + CInt(Mid(Barcode, i, 1))
    Next i
    
    Total = TotalOdd + TotalEven
    Mod10CheckDigit = 10 - IIf(Right(Total, 1) = 0, 10, _
          Right(Total, 1))
    
    
End Function
Thanks in advance

Hi Everyone,

My company is in the process of creating UPC barcodes for our product lines. We use the GTIN-12 format - eleven digits plus the calculated twelfth check digit. Right now, we're entering the 11 digits into an online check digit calculator to get that last number; the problem is, we have about 40,000 items to go and none of us want to type all of that into a calculator.

This is the formula:
N1 through N11 signify the digits in the code
(N1*3)+(N2*1)+(N3*3)+(N4*1)+(N5*3)+(N6*1)+(N7*3)+(N8*1)+(N9*3)+(N10*1)+(N11*3)
Take the sum of this and subtract it from the nearest equal or higher multiple of 10.

Example:
74702813169
(7*3)+(4*1)+(7*3)+(0*1)+(2*3)+(8*1)+(1*3)+(3*1)+(1*3)+(6*1)+(9*3)=102
110-102=8
Complete barcode: 747028131698

Is it possible to calculate this in Excel? The problem I'm finding is identifying what to subtract the sum from, the nearest equal or higher multiple of 10. There must be a way, it's just that none of us know how.

I hope I've explained this clearly enough. Can anyone help us? It would be greatly appreciated!

Brianna

Hi guys,

I am trying to calculate check digits (Modulus 97 check) for an IBAN
creation demo and am running in serious problems with Excel. First approach
I tried is to use the MOD-function however this seems to be not possible
using numbers with 18 digits.

Next approach I tried is a "step by step" calcualtion however the Excel
results are NOT correct when dividing numbers with 18 digits.

E.g. 210005665660111000 / 97 = 2165006862475370.00 -> rcorrect result should
be 2165006862475371.1340

Does anybody have any experience with this discrepancies or with check digit
calculations in Excel.

Thanks much for all your help.

Ritesh


No luck finding an answer? You could always try Google.