Validate Check Digits in Excel Data with Python

A product list with a mistyped barcode, a supplier sheet with a wrong IBAN, a customer file with a reference number missing a digit: each of these is cheap to catch at import and expensive to discover after a shipment goes to the wrong place or a payment bounces. Many identifiers include a check digit — a last digit computed from the others — precisely so that software can catch typing errors without looking anything up. Swapped neighbouring digits and single wrong digits, the two most common typos, fail the check. This page validates three widespread schemes in a spreadsheet column: GTIN/EAN barcodes, IBANs and the Luhn algorithm, and marks the failures in the workbook. It belongs to Validating Excel Data with Python.

Prerequisites

pip install pandas openpyxl python-stdnum

suppliers.xlsx lists six suppliers with an IBAN, a product barcode and a card-style reference number. Some values are valid, some have a single wrong digit, some contain spaces. The IBANs and card numbers are the standard published test numbers, not real accounts. python-stdnum is used at the end to cross-check the hand-written validators.

Step 1 — Read identifiers as text

import pandas as pd

sup = pd.read_excel("suppliers.xlsx", dtype=str)
print(sup.dtypes.to_dict())
print(sup[["Barcode", "CardRef"]].head(3).to_string(index=False))

Read every identifier column with dtype=str. Read as numbers, a barcode starting with 0 loses its leading zero, and a 16-digit card reference loses its last digit to floating-point rounding — Excel and pandas both keep only about 15 significant digits. Either change makes a valid number fail its check. If the file was saved by Excel with such columns stored as numbers, the digits may already be gone; the cure is in Fix Long ID Numbers Losing Digits in Excel.

Step 2 — GTIN / EAN-13 barcodes

The last digit of a GTIN (EAN-8, UPC-A, EAN-13, GTIN-14) makes a weighted sum a multiple of ten: working from the right, digits are weighted 3, 1, 3, 1… with the check digit itself weight 1.

def gtin_valid(code):
    digits = "".join(ch for ch in str(code) if ch.isdigit())
    if len(digits) not in (8, 12, 13, 14) or digits != str(code).replace(" ", ""):
        return False
    body, check = digits[:-1], int(digits[-1])
    total = sum(int(d) * (3 if i % 2 == 0 else 1) for i, d in enumerate(reversed(body)))
    return (10 - total % 10) % 10 == check

sup["Barcode OK"] = sup["Barcode"].map(gtin_valid)
print(sup[["Supplier", "Barcode", "Barcode OK"]].to_string(index=False))

4006381333931 is valid; 4006381333932 differs only in its last digit and fails. The barcode with a leading zero, 0012345678905, passes — it would have failed if the column had been read as a number. Reversing the body puts the digit next to the check digit at index 0, where it gets weight 3 — the same rule for every GTIN length. Rejecting codes whose length is wrong or which contain anything other than digits catches truncation and stray characters before the arithmetic.

Step 3 — IBANs with the mod-97 check

An IBAN is valid when, after moving the first four characters to the end and turning letters into numbers (A = 10, B = 11, … Z = 35), the resulting number leaves a remainder of 1 when divided by 97:

IBAN_LENGTH = {"DE": 22, "ES": 24, "DK": 18, "GB": 22, "FR": 27, "NL": 18, "IT": 27, "BE": 16}

def iban_valid(value):
    s = str(value).replace(" ", "").upper()
    if len(s) < 15 or not s[:2].isalpha() or not s[2:4].isdigit():
        return False
    if s[:2] in IBAN_LENGTH and len(s) != IBAN_LENGTH[s[:2]]:
        return False
    rearranged = s[4:] + s[:4]
    number = "".join(str(int(ch, 36)) for ch in rearranged)
    return int(number) % 97 == 1

sup["IBAN OK"] = sup["IBAN"].map(iban_valid)
print(sup[["Supplier", "IBAN", "IBAN OK"]].to_string(index=False))

int(ch, 36) converts "0"–"9" to 0–9 and "A"–"Z" to 10–35 in one step. Python integers have no size limit, so the 30-plus-digit number is computed exactly — something a spreadsheet formula cannot do, which is why IBAN checks in Excel need piecewise MOD tricks. Checking the country-specific length catches a dropped digit even in the rare case where the remainder happens to work out. The second German IBAN, one digit different from the first, fails; the British one passes with or without spaces.

Step 4 — The Luhn algorithm

Payment card numbers, many loyalty and membership numbers and some national IDs use the Luhn (mod 10) check: double every second digit from the right, subtract 9 from any result above 9, and the total must be a multiple of 10:

def luhn_valid(value):
    digits = str(value).replace(" ", "")
    if not digits.isdigit() or not 12 <= len(digits) <= 19:
        return False
    total = 0
    for i, d in enumerate(reversed(digits)):
        n = int(d)
        if i % 2 == 1:
            n *= 2
            if n > 9:
                n -= 9
        total += n
    return total % 10 == 0

sup["CardRef OK"] = sup["CardRef"].map(luhn_valid)
print(sup[["Supplier", "CardRef", "CardRef OK"]].to_string(index=False))

The standard test numbers pass; 4539148803436468, the first number with its last digit changed, fails. Luhn catches every single-digit error and most swaps of adjacent digits — except swaps of 0 and 9, a known blind spot. Never store or process real card numbers in spreadsheets; the check is shown here because the same algorithm validates many non-sensitive reference numbers.

Step 5 — Cross-check with python-stdnum

Hand-written validators are short, but a second opinion from a maintained library is cheap insurance — and python-stdnum covers hundreds of other formats (VAT numbers, ISBNs, national IDs):

from stdnum import ean, iban, luhn

lib = pd.DataFrame({
    "Barcode": sup["Barcode"].map(ean.is_valid),
    "IBAN": sup["IBAN"].map(iban.is_valid),
    "CardRef": sup["CardRef"].map(lambda v: luhn.is_valid(v.replace(" ", ""))),
})
mine = sup[["Barcode OK", "IBAN OK", "CardRef OK"]].set_axis(["Barcode", "IBAN", "CardRef"], axis=1)
print("hand-written validators agree with python-stdnum:", mine.equals(lib))
print(lib.sum().to_dict(), "valid of", len(sup))

The two agree on every value. stdnum.iban also knows every country's IBAN length and structure, so in production code using the library is the better choice for IBANs; the hand-written versions are useful where you want no dependency, or need to explain the rule to someone. For format validation without check digits — email addresses, phone numbers — see Validate Email and Phone Formats in Excel Data.

Step 6 — Highlight the invalid cells

The people who fix the data need to see the failures in their own workbook. Write the sheet with each invalid cell highlighted and a summary column:

from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font

wb = load_workbook("suppliers.xlsx")
ws = wb.active
bad = PatternFill("solid", fgColor="FCE9E9")
red = Font(color="9C0006", bold=True)
cols = {"IBAN": "IBAN OK", "Barcode": "Barcode OK", "CardRef": "CardRef OK"}
header = {c.value: c.column for c in ws[1]}
ws.cell(1, ws.max_column + 1, "Problems")
problem_col = ws.max_column
for r, (_, row) in enumerate(sup.iterrows(), start=2):
    problems = [col for col, flag in cols.items() if not row[flag]]
    for col in problems:
        ws.cell(r, header[col]).fill = bad
        ws.cell(r, header[col]).font = red
    ws.cell(r, problem_col, ", ".join(problems) or "ok")
wb.save("suppliers_checked.xlsx")
print(pd.read_excel("suppliers_checked.xlsx")[["Supplier", "Problems"]].to_string(index=False))

Each failing cell is shaded red and the last column lists the problems per supplier, so the sheet can be filtered to the rows that need attention. The general pattern is covered in Highlight Invalid Cells in Excel with Python.

Common pitfalls

• Reading identifiers as numbers. Leading zeros and digits beyond 15 are lost, and valid codes fail.
• Validating with spaces or dashes. Strip formatting characters before computing, but reject other characters.
• Confusing valid with real. A correct check digit only means no typing error was detected; it does not prove the account or product exists.
• Real card numbers in spreadsheets. Do not store them; use the check only for non-sensitive references.
• Ignoring length rules. Country-specific IBAN lengths and GTIN lengths catch errors the checksum alone might miss.

Conclusion

Check digits let a script catch most typing errors in identifiers without any lookup. Read the columns as text, then validate GTIN/EAN barcodes with alternating 3·1 weights modulo 10, IBANs by rearranging, converting letters and testing for a remainder of 1 modulo 97, and Luhn numbers by doubling every second digit. Cross-check against python-stdnum, which covers many more formats, and highlight the failing cells in the workbook so the owners can fix them at the source.

Frequently asked questions

How do I validate an EAN-13 barcode in Python? Weight the first twelve digits alternately 1 and 3 from the left (3 and 1 counted from the right), sum them, and check that (10 - sum % 10) % 10 equals the thirteenth digit. stdnum.ean.is_valid does the same.

How do I check whether an IBAN is valid with Python? Remove spaces, move the first four characters to the end, replace each letter with its number (A=10 to Z=35), and check that the resulting integer modulo 97 equals 1. Also check the country's IBAN length; stdnum.iban.is_valid handles both.

What is the Luhn algorithm? A mod-10 check used by card numbers and many IDs: from the right, double every second digit, subtract 9 from results above 9, add everything up, and the number is valid if the total is divisible by 10.

Why do valid barcodes fail validation after reading the Excel file? They were read as numbers, so leading zeros were dropped or long values were rounded. Read identifier columns with dtype=str in pd.read_excel.

Related

• Up one level: Validating Excel Data with Python — checking data before it is used.
• Validate Excel Rows with pydantic Models — wrapping such checks in a schema.
• Keep Leading Zeros When Reading Excel with pandas — reading identifiers safely.
• Generate QR Codes and Barcodes in Excel with Python — producing barcodes from validated codes.