Prepare the column: what Excel does to numbers
This article shows how to prepare the column, run a first sort with formulas, then check every row for good, without copying numbers one by one.
Excel reads a SIREN, a SIRET or a Belgian enterprise number as a number and treats it that way. Three things follow:
- the leading zero disappears: the Belgian number
0417497106becomes417497106, which looks like a 9-digit SIREN; - long numbers show in scientific notation: in the General format, a 14-digit SIRET appears as
5.52033E+13in a narrow column. The number is kept, but it becomes unreadable and is easily mistyped; - spaces and dots stay when the number was pasted from a PDF:
552 032 534is then no longer a number, and formulas treat it differently from its neighbours.
First, select the column and choose the Text format. For a CSV file, use Data, From Text/CSV and set the number column to Text, rather than opening the file with a double click. If the zeros are already gone, a formula restores them: =TEXT(A2,"000000000") for a SIREN, =TEXT(A2,"00000000000000") for a SIRET and =TEXT(A2,"0000000000") for a Belgian number.
A first sort with formulas
A few formulas are enough to spot the rows that need attention, before any check. Assuming the cleaned number is in column B:
| Need | Formula |
|---|---|
| Remove spaces | =SUBSTITUTE(A2," ","") |
| Count the digits | =LEN(B2): 9 for a SIREN, 14 for a SIRET |
| SIREN of a SIRET | =LEFT(B2,9) |
| French VAT number from the SIREN | ="FR"&TEXT(MOD(12+3*MOD(B2,97),97),"00")&B2 |
The last formula applies the VAT key calculation: for Danone's SIREN, 552032534, it returns FR27552032534. Compare the result with the VAT number on the customer record: a gap almost always means a typo or the number of another company of the group. The full format is explained in the French VAT number.
The check digit of the SIREN and SIRET (the Luhn algorithm) can be written as a formula too, but it is long and fragile to maintain. It is simpler to leave it to the check of the next step.
What a formula cannot tell you
A well-formed number with a right check digit can still be wrong for your file. No formula knows:
- whether the number was really issued;
- whether the company or site is still active, or closed months ago;
- whether the VAT number is registered;
- whether the number belongs to your customer, and not to a namesake or another company of the group.
These four answers require looking up the official registers, row by row. That is the next step. For a single number, checking a SIRET details the checks.
Three ways to check the whole workbook
Depending on the size of the file and how often you need it, three methods, without copying numbers one by one:
- Upload the file on the Customer file page: an Excel workbook, a CSV or a text file. The diagnosis is free and needs no account: it flags invalid numbers, closed companies and duplicates. The corrected file is paid, from €29 excluding VAT, with no subscription.
- Check from Excel with the Jsonpage Excel templates: a Power Query script for desktop Excel or an Office Script for Excel on the web. You turn the list into a table, paste the script with your key, and Excel adds the official name, SIREN, status, VAT number, its status and the address to each row. Data, Refresh All runs the check again when the list changes.
- Use the API from your own software, to check numbers as they are typed rather than afterwards.
The last two methods need an API key: the free account gives one, with no credit card, and 1,000 requests a day.
Example: five rows, five cases
Here are five real numbers as they appear in an export, and what the check says about them:
| Typed in the file | What the check shows | Fix |
|---|---|---|
552032534 | Danone's SIREN, an active company | None. VAT number derived: FR27552032534 |
55203253400646 | Right check digit, but the site closed on 19 August 2025 (former head office) | Replace with the current head office SIRET, 552 032 534 00703 |
552120223 | Wrong check digit: a typo | Ask the customer again: Société Générale's SIREN is 552 120 222 |
417497106 | Only 9 digits: a Belgian number whose leading zero was dropped | 0417497106, so BE0417497106 for VAT (AB InBev) |
5.52033E+13 | A 14-digit SIRET shown in scientific notation | Text format on the column: the full number comes back |
Frequently asked questions
Why does Excel show a SIRET as 5.52033E+13?+
In the General format, Excel shows long numbers in scientific notation. The 14-digit SIRET is kept but unreadable: set the column to Text, or to the custom format 00000000000000.
How do I restore the lost zero of a Belgian number?+
With =TEXT(A2,"0000000000"): the number is padded to 10 digits. 417497106 becomes 0417497106 again.
Is a formula enough to check a SIREN?+
No. A formula spots a malformed number, but not a closed company, a number never issued or one that belongs to another company. You need to look up the official registers.
Can I check an Excel file without an account?+
Yes. The diagnosis on the Customer file page is free and needs no account; only the corrected file is paid.
Keep reading
- CUSTOMER FILEClean Your Customer File for E-InvoicingCheck SIRENs, spot closed companies and duplicates before e-invoicing: a 7-step method, free diagnostic, corrected file from €29 excl. VAT.
- FILEFind your suppliers' SIREN numbers from a fileAbbreviated names, VAT numbers without prefix, mistyped SIREN: the method to find the right SIREN for every row of a file.
- SIRETHow to check a French SIRET numberThe 14-digit format, spotting typing mistakes and the difference between a valid SIRET and an active establishment.