Check digit formula for EAN, UPC and GTIN codes in Excel and Google Sheets
Two formulas, tested in Excel and Google Sheets on the same set of codes. The first goes next to a column of codes and says what is wrong with each one. The second adds the check digit to a code that does not have it yet.
Which formula checks a whole column of codes?
Put the codes in column A, starting in row 2. Paste the formula into B2 and fill it down: drag the small square in the corner of the cell, or double-click it. Triple-click the formula below to select all of it.
=IFERROR(IF(A2="","",IF(ISNUMBER(SEARCH("E+",A2)),"scientific notation",IF(ISERROR(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1)),"not only digits",IF(OR(LEN(A2)=8,LEN(A2)=12,LEN(A2)=13,LEN(A2)=14),IF(MOD(SUMPRODUCT(MID(RIGHT(REPT("0",14)&A2,14),ROW(INDIRECT("1:14")),1)*(1+2*MOD(ROW(INDIRECT("1:14")),2))),10)=0,"OK","wrong check digit"),"wrong length: "&LEN(A2))))),"not only digits")| Result | What it means |
|---|---|
OK | 8, 12, 13 or 14 digits, and the check digit matches, e.g. 5901234123457 |
wrong check digit | the last digit does not follow from the others — usually one mistyped digit, e.g. 5901234123450 |
wrong length: 11 | a different number of digits; 11 is usually a UPC-A that lost its leading zero, e.g. 12345678905 |
not only digits | a space, a hyphen, a letter, a non-breaking space, a placeholder such as “n/a”, or an error from another formula, e.g. 590-1234-12345 |
scientific notation | the code is stored as text like 5.90123E+12, and its last digits are gone |
OK means the code is well formed. Whether it exists is another question — more on that below.
Which formula adds the check digit?
This one is for codes without their last digit: 7 digits for an EAN-8, 11 for a UPC-A, 12 for an EAN-13 and 13 for a GTIN-14. The result is text, so the spreadsheet will not turn it into 5.90123E+12 or drop a leading zero.
=IFERROR(IF(A2="","",IF(ISERROR(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1)),"not only digits",IF(OR(LEN(A2)=7,LEN(A2)=11,LEN(A2)=12,LEN(A2)=13),A2&MOD(-SUMPRODUCT(MID(RIGHT(REPT("0",13)&A2,13),ROW(INDIRECT("1:13")),1)*(1+2*MOD(ROW(INDIRECT("1:13")),2))),10),"wrong length: "&LEN(A2)))),"not only digits")| In column A | Result |
|---|---|
590123412345 | 5901234123457 |
9638507 | 96385074 |
01234567890 | 012345678905 |
1590123412345 | 15901234123454 |
Use it only on codes that have no check digit yet. A complete EAN-13 also has 13 digits, so the formula would add a fourteenth one, and the result would not be a code of that product.
Commas or semicolons?
The formulas above separate arguments with commas, which is how Excel and Google Sheets write them with English regional settings (US, UK). Where the decimal separator is a comma, as in most of Europe, both programs want semicolons. Replace every comma with a semicolon: there is no comma inside the quoted text of these formulas, so Find and Replace will not break anything.
Google Sheets accepts English function names even when it shows them in another language — we checked with Polish. Excel does not: Excel in another language expects the function names in that language. The Polish version is on the Polish page.
How do the formulas work?
Both start by padding the code with zeros to 14 digits: RIGHT(REPT("0",14)&A2,14). Zeros in front
do not change the check digit, so one formula covers EAN-8, UPC-A, EAN-13 and GTIN-14.
MID with ROW(INDIRECT("1:14")) takes the digits one by one, and 1+2*MOD(ROW(…),2) gives them their
weights: 3 in odd positions, 1 in even ones. In a 14-digit code this is the usual rule — from the
right, 3 and 1 in turn, starting with the digit just before the check digit. SUMPRODUCT multiplies
and adds. The check digit itself is in the sum with weight 1, so in a correct code the total is a
multiple of 10, which is what MOD(…,10)=0 tests.
The second formula does the same with 13 positions and appends MOD(-sum,10): what the sum needs to
reach the next multiple of ten, or 0 when it already is one. The method itself, with a worked
example, is on the GTIN validator page.
The formulas are long for a reason. They have no array constant such as {3,1,3,1}, because Excel in
some languages uses a different separator inside it: Polish Excel wants {3\1\3\1} and refuses the
version with commas. They have no LET or SEQUENCE either, because Excel 2019 and older do not have
them. The newest function in them is IFERROR, from Excel 2007, and SUMPRODUCT needs no
Ctrl+Shift+Enter.
Before you paste codes into a spreadsheet
Excel and Google Sheets take a code for a number. Leading zeros vanish, and Excel shows a 13-digit
number as 5.90123E+12. As long as the cell holds the number, nothing is lost: the formula reads the
full value and says OK. The digits are lost when Excel saves the sheet to CSV, because the file
then gets the text 5.90123E+12. A code like that can only come back from where it came from — the store,
the supplier’s file, the packaging.
To keep codes exactly as they are, set the column to text before you paste: in Excel, Home → Number Format → Text; in Google Sheets, Format → Number → Plain text.
What don’t the formulas check?
- Whether the number exists and belongs to this product. Only the GS1 registry knows that — look a code up in Verified by GS1.
- Ranges Google Merchant Center does not accept: internal codes starting with 2, 02 or 04 and coupon
codes starting with 05, 98 or 99 (in the 13-digit form). The formula says
OKto them. - Duplicates, including the same code written two ways, such as
012345678905and0012345678905. - A code made of zeros:
0000000000000has a correct check digit. - ISBN-10, which has its own check digit and can end in X. The formula reports it as a wrong length or as not only digits. An ISBN-13 is an EAN-13 and is checked like one.
How do I check a whole file at once?
The GTIN validator does everything on that list except the GS1 registry, with no formulas: paste a column or open the store export as CSV or XLSX. It finds duplicates also when they are written differently, names the invisible character in a code and gives the row number of each problem. The file never leaves your browser.
In Google Sheets, the free GTIN & EAN Check add-on does the same without leaving the sheet: select the column and choose Extensions → GTIN & EAN Check → Check selected codes. The codes to fix go to a report in a new sheet, with a link to each cell.