Google Answers Logo
View Question
Q: Need an excel formula to create a check digit for GTIN-12 - see example in link ( Answered 5 out of 5 stars,   3 Comments )
Subject: Need an excel formula to create a check digit for GTIN-12 - see example in link
Category: Business and Money
Asked by: captcliff-ga
List Price: $10.00
Posted: 13 Oct 2006 09:02 PDT
Expires: 12 Nov 2006 08:02 PST
Question ID: 773211
Subject: Re: Need an excel formula to create a check digit for GTIN-12 - see example in link
Answered By: maniac-ga on 13 Oct 2006 18:15 PDT
Rated:5 out of 5 stars
Hello Captcliff,

Hmm. I tried the comment from reinedd, but it did not work on my system.

Let's work out a few different ways to solve this.

[1] If you can use five cells - you can reproduce the steps described
at the web site you referenced in the following way:
 - A1 has your 11 digit value (step 1)
 - B1 has the sum of odd digits (step 2)
 - C1 has 2x the results of step 2
 - D1 has 1x the even digits (step 4)
 - E1 has the sum of steps 3 & 4 (step 5)
 - F1 computes the check digit (step 6)
The last computes the remainder after dividing E1 by 10, if zero -
return zero, otherwise subtract that remainder from 10 which is the
smallest number that will round the result of step 5 up to a multiple
of 10.

You can compress this quite easily into fewer cells - two is perhaps
the most efficient with
[note - I relocated the "two cells" to allow an easy comparison
between the two methods]

[2] The "two cell" solution
 - A1 has the 11 digit number
 - H1 has steps 2 though 5 - split into two lines, combine into a single cell.
 - G1 has

In this way, G1 and F1 are adjacent and should have the same result
for easy comparison.

If you must do this with a single formula, the following can be used
which repeats the formula in H1 twice in the IF statement. Its been
split into several lines to make it a little more clear the parts of
the formula - combine into a single line / single cell for proper


Let's walk through the parts of the formula so you can build similar
formulas for other needs. I suggest you search the Excel online help
for additional information on these functions.

The IF function takes three values (V1, V2, V3). If V1 is true, the
result is V2; if V1 is false, the result is V3.

The MOD function takes two values (V1, V2). The result is the
remainder of dividing V1/V2. In our case, we're dividing by 9, so the
result is between 0 and 9 inclusive.

The MID function takes three values (V1, V2, V3). MID normally
operates on a string valued V1 - but Excel will also convert a number
to a string automatically. The result is the sequence of V3 letters of
V1 starting at position V2. In this example, we always extract a
single letter (V3=1) at a specific position - different for each MID
function call.

The + operator normally operates on numbers, but Excel also
automatically converts strings (letters) to numbers so the digits of
the value are summed properly.

I've compared several values to the results from that web page you
referenced. The formulas (all three forms) appear to work quite well -
and produce consistent results.

If you have any problems understanding the answer or if you need more
information on some part of the question I did not adequately cover -
please make a clarification request. I would be glad to help further.

Good luck with your work.


Request for Answer Clarification by captcliff-ga on 13 Oct 2006 23:27 PDT
Very well done. Thank you. 

It has been awhile since I used this service. I did not see a way to
pay you so I am assuming that the money is paid automatically now.

If someone is not familier with, how would they find it
as I do not see it listed under "more" any longer?

Clarification of Answer by maniac-ga on 14 Oct 2006 09:28 PDT
Hello Captcliff,

Thanks for the kind words.

About finding Google Answers, I suggest you take that question to the
editors / support staff for Google Answers directly.  You'll find the
email link at the bottom of each question page.

captcliff-ga rated this answer:5 out of 5 stars and gave an additional tip of: $5.00
Maniac-ga was quick, through, and explained the solution is such a way
that I could build on it to solve other questions. I was impressed!!!

Subject: Re: Need an excel formula to create a check digit for GTIN-12 - see example in link
From: reinedd-ga on 13 Oct 2006 10:23 PDT
if your value is on A1

you put

Subject: Re: Need an excel formula to create a check digit for GTIN-12 - see example in l
From: captcliff-ga on 13 Oct 2006 23:33 PDT
Just wanted to thank reinedd-ga for his contribution and comment.
Subject: Re: Need an excel formula to create a check digit for GTIN-12 - see example in l
From: jgbix-ga on 24 Oct 2006 13:45 PDT
Hi guys,

I looked at your formula and though it does solve the problem its a bit lengthy.



Type the 11 digit code in cell A1, and enter this formaula in A2,
replicate the formula by copying down.  The formula may be modified
for 12 or 13 digit codes by  adding integers (odd or even) within the
brackets { } as appropriate. Use CONCATENATE to add the two text
strings together in A3 and you are done...

Hope this helps

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy