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.

- ISBN 13 to 10 conversion Macro
- POSTNET Function
- Check If String Contains Only Digits
- Check Number Uses Unique Digits
- Check If First 5 Digits Of String Are Numeric
- Count & check cells in a range for exact number of digits
- Check Digit Calculation
- Code to Check for 2 digit Number
- How to check 4 digit text?
- Macro to check digit of revenue code
- Calculating UPC Barcode Check Digits
- Check digits
- I have a formula that gives you the check digit for your UPC
- Mod 11 formula for valid check digit
- Creating a Check Digit
- How can I Calculate Check Digit for UPC A - the 13th warrior
- Excell Check Digit Formula
- ISBN Check Digits
- Mod-10 Check Digit
- Access 2007 - check digit calculation for 11 digit upc code in select query
- Check digits between column
- Combining two equations with MOD function for UPC check digit calculation.
- GTIN Barcode Check Digit Help
- Check Digit calculation (IBAN) - Discrepancies when using numbers with 18 digits or MOD-function

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

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

VB: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?IsNumeric(MyString)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

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

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 ccI 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"

Thanks,

Meimei

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 !

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

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

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

UPC Code: 8002810001 Check Digit:6

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]

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

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

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.

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

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

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,

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

I wanted to combine these if possible,

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

and this codewhich just calculates the check digit.Thanks in advance

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

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

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