INSEE number and control key in Excel

Hello

While searching on the Internet, I found this:
"The 2-digit control key is equal to 97 - (the remainder of the Euclidean division of the NIR by 97), the NIR is the 13-digit National Identification Number from Insee, which is unique.
This algorithm also works with temporary numbers assigned by social security (starting, for example, with a sex code=8...)."
See you later

The (good) response from LePierre, whom I salute, arrived while I was writing mine.

It should be noted that Alexandre MOATTI did the work for us in Excel:
http://www.maths-et-physique.net/article-6061182.html

His file is so simple (6 cells) that I wonder why bother creating a function under macro!
--
Retirement is nice! Especially in the Antilles... :-)
☻ Raymond ♂

Hello,

if we consider the 13-digit INSEE number and call r1, r2... r13 the 13 digits that make up this number,
the key is calculated as follows:

97 - MOD (((r1r2r3r4)*34+(r5r6r7)*30+(r8r9r10)*27+(r11r12r13)),97)

r1r2r3r4 is the concatenation of the first 4 digits of the INSEE number
r5r6r7 is the concatenation of the 5th, 6th, and 7th digits
r8r9r10 is the concatenation of the 8th, 9th, and 10th digits
r11r12r13 is the concatenation of the 11th, 12th, and 13th digits

therefore, in a spreadsheet like EXCEL, if the INSEE number is in A1, the key:

---------------------------------------------------------------------------
=97-MOD(MID(A1,1,4)*34+MID(A1,5,3)*27+MID(A1,8,3)*30+MID(A1,11,3)*1,97)
---------------------------------------------------------------------------

if we prefer to separate the different pieces of information or if they are necessarily separated (form with multiple fields):

r1 : sex code
r2r3 : birth year
r4r5 : birth month
r6r7 : birth department
r8r9r10 : municipality code
r11r12r13 : order number

the key is calculated as follows:

97 - MOD (((r1)*50+(r2r3)*49+(r4r5)*81+(r6r7)*27+(r8r9r10)*30+(r11r12r13)),97)

and, in a spreadsheet, if the INSEE number is in A1, the key:

-----------------------------------------------------------------------------------------------------------
=97-MOD(MID(A1,1,1)*50+MID(A1,2,2)*49+MID(A1,4,2)*81+MID(A1,6,2)*27+MID(A1,8,3)*30+MID(A1,11,3)*1,97)
-----------------------------------------------------------------------------------------------------------

The demonstration is inspired by what I found on WIKIPEDIA regarding the calculation of the RIB key.

I'll spare you here, but if you're interested, we can discuss it: o)

Samuel LANDIS

Thank you, Pierre and Raymond,

Yes, due to lack of time, I'm responding to you at the same time.

LePierre, I know how to calculate the INSEE by hand and find its key.

I just wanted a formula in Excel, Raymond, not a page of code. The searches I did on Google did not answer my question.
In the end, I found the formula myself: key = 97-MOD(number, 97). The problem is that I don't know how to set up my Excel because it doesn't want to accept displaying 13-digit numbers, and so I get #number#!

Any suggestions? Simple, please ...

Cristina

Thank you Raymond, thank you Eric,

Your answers are greatly appreciated.

Thanks to both of you, I think I have found the answer to this question. But (there's always a "but") I need to continue to create a spreadsheet in Excel to calculate the check digit for ISBN, ISSN codes, and bank accounts (modulo 97).
I'm told to use the MOD function, I think I've already understood that, but there must be some formulas beforehand to calculate the weighting.

Please be "gentle" with me during the explanations, I am studying economics and not computer science, but I have a computer science exam to take in March, under the conditions where teaching is remote, with no teacher, and with very basic and poorly explained courses. So, I'm struggling and I persist. I have no choice.

Thank you in advance,
Cristina

Hello, cristina, and welcome to this forum.

But if you're in a hurry, you would have saved time by doing what I just did for you, typing "N° INSEE" on Google! You get the address https://www.google.com/search?q=N%C2%B0+INSEE&rls=com.microsoft:fr:IE-SearchBox&ie=UTF-8&oe=UTF-8&sourceid=ie7&rlz=1I7HPEB_fr&gws_rd=ssl which contains 3,370,000 responses, including 3,000,000 in French: so you have choices!

As for me, I found for example
https://codes-sources.commentcamarche.net/ for the 9-digit SIREN number, and also
https://codes-sources.commentcamarche.net/ which answers your question (except for the case of Corsica) but which is a macro in VBA.
It was in https://codes-sources.commentcamarche.net/

And to the next question...
--
It's great, retirement! Especially in the Caribbean... :-)
☻ Raymond ♂