INSEE number and control key in Excel Solved

Question

Hello,
I have been asked to create a spreadsheet in Excel that, when I type the first 13 characters of an INSEE number, returns the value of the control key.

Thank you in advance for your quick response. I'm seriously panicking, I have an Applied Computer Science exam in data processing coming up and I'm far from being able to do it.

Cristina

Configuration: Windows XP Internet Explorer 7.0

eriiic · Accepted Answer

Hello everyone,

You have #number because mod() is somewhat overwhelmed by the size of the number.
You can bypass it with:
=97-ROUND((A2/97-INT(A2/97))*97,0)

And to always display your input in 13 digits, enter your number with a ' in front or set your cell to text format.

eric

LePierre · Answer

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

Raymond PENTIER · Answer

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... :-) &#9787; Raymond &#9794;

cristina newton · Answer

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

Raymond PENTIER · Answer

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... :-) &#9787; Raymond &#9794;

cristina newton · Answer

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

sam_island · Answer

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

INSEE number and control key in Excel

7 réponses

Samsung j5 unable to answer

Scam of the suvedene highway

No sound with ps3 and hdmi

Wake up from sleep mode in windows 10.

Internet connection problem with my ipad

Free box: remote control broken

Suspicious number text message

I receive calls from unallocated numbers.

Minecraft restore the cursor and the bar.

My ps3 keeps shutting down on its own...