Validate personal ID numbers in a table: SIN, NINO, PESEL, CPF and others
An HR export, a patient list or a customer file with national ID numbers always has typos in it: a transposed pair of digits, a missing character, a letter where a digit belongs. Most countries build a check digit into the number precisely to catch that. A filter cannot use it — you cannot write a checksum as a condition — but the table viewer can compute it for every row at once.
Free for personal use. “Data” → “Validate values”. Windows version — 3.5 MB, no installation: details. Organizations: license.
How to run the check
- Open the table — CSV, Excel, DBF, XML, JSON or SQLite.
- Choose “Data” → “Validate values”. A column named
sin,nino,dni,pesel,cpf,aadhaarand so on gets its rule automatically; otherwise pick the format by hand. - Press “Validate”. The new “Check” column explains what is wrong in each bad row and stays empty in good ones.
- “Show rows with errors” leaves only the rows that need a human.
If a numeric column has swallowed leading zeros (a PESEL of 05210101230 became 5210101230), the check puts them back before computing, so a lost zero is not reported as an error.
What is checked for each country
| Number | Shape | What the check catches |
|---|---|---|
| US SSN | 9 digits | no checksum exists; area 000, 666 and 9xx, group 00 and serial 0000 are never issued |
| Canada SIN | 9 digits | the Luhn check digit — the same algorithm as on payment cards |
| UK National Insurance number | 2 letters, 6 digits, a letter A–D | no checksum; letters never used in the prefix, and prefixes such as BG, GB, NK, KN, TN, NT and ZZ |
| Spain DNI / NIE | 8 digits and a letter; or X, Y or Z, 7 digits and a letter | the control letter: the number modulo 23 picks it from a fixed alphabet (for an NIE, X, Y and Z count as 0, 1 and 2) |
| Italy Codice Fiscale | 16 characters | the last letter, computed from the first 15 with separate value tables for odd and even positions; letters that replace digits in the rare homonym cases are accepted |
| France NIR | 13 digits and a 2-digit key | the key equals 97 minus the number modulo 97; Corsican departments 2A and 2B are counted as 19 and 18 |
| Poland PESEL | 11 digits | the birth date written inside the number must exist (the month also encodes the century), and the last digit is a weighted checksum |
| Germany Steuer-ID | 11 digits, not starting with 0 | the check digit by ISO 7064, the MOD 11,10 scheme |
| India Aadhaar | 12 digits, first 2–9 | the Verhoeff check digit, which catches every single-digit error and every swap of neighbouring digits |
| Brazil CPF | 11 digits | both check digits; numbers made of one repeated digit pass the sum but are never issued, so they are rejected |
Spaces, dashes, dots and slashes are ignored, and letter case does not matter, so 046 454 286, 046-454-286 and 046454286 are all the same number.
Why a check digit is better than “9 digits”
A length check only rejects values of the wrong shape. A typo — one wrong digit or two neighbours swapped — leaves the shape intact. A check digit is chosen so that almost any such slip breaks the arithmetic. Luhn, used by the Canadian SIN, catches every single wrong digit and nearly all swaps of neighbours; Verhoeff, used by Aadhaar, catches all of both.
Some numbers have no check digit by design, like the US SSN and the UK NINO. For those only the issuing rules can be verified, and the tool says so in each format’s hint instead of implying more certainty than it has.
What a passing number does not mean
It means the number is well formed and has no typo. It does not mean the number was issued, belongs to this person, or is still valid — only the issuing authority can say that. And a number made up by someone who knows the algorithm will pass: the check protects against mistakes, not against fraud.
National ID numbers are personal data. The check runs entirely in your browser tab and the file is never uploaded, which is the point of doing it here rather than in an online validator.
FAQ
Is the file sent anywhere?
No. Validation runs in your browser tab; the page cannot send the file’s contents to any other address.
My column is named differently. Can I still use these formats?
Yes. Pick the format for the column by hand in the validation dialog. The rule is remembered by column name for the next file with the same columns.
A genuine number is reported as invalid. What now?
Read the message first: it says which part failed — length, a character, the date or the check digit. Usually a spreadsheet has altered the value (a leading zero lost, a long number turned into scientific notation) or there is a stray character. The check does not guess what was meant.
Can I validate a country that is not in the list?
Describe it in “My formats” as a pattern of parts or a regular expression. That checks the shape; checksums are available only for the built-in formats.
Does the Canadian SIN check care about the first digit?
No, it looks at the digits and the check digit only. 046 454 286 is the widely used example and passes.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.