BSN met Excel formule controleren
Deze blog geeft antwoord op de vraag: Hoe controleer ik een burgerservicenummer (BSN) met een Excel formule.
Door de lengte van het burgerservicenummer is dit nummer lastig in te voeren. Een fout is zo gemaakt. Dan is het prettig als Excel met een foutmelding komt, of dat een fout burgerservicenummer een andere celkleur krijgt.
Speciaal voor het controleren van burgerservicenummers staan in deze blog 2 Excel formules.
De eerste formule geeft aan of een burgerservicenummer correct is. De 2e formule geeft juist aan of een burgerservicenummer fout is.
Excel formule voor de validatie van een BSN.
Voor de validatie van een burgerservicenummer kan je onderstaande formule kopieren en plakken in het dialoogvenster voor gegevensvalidatie. (Hierbij A1 vervangen door de verwijzing naar de cel die gecontroleerd moet worden). Deze Excel formule controleert of een burgerservicenummer correct is ingevoerd.
=EN(LENGTE(A1)=9;ISGETAL(A1*1); ALS.FOUT(REST((DEEL(A1;1;1)*9)+(DEEL(A1;2;1)*8)+(DEEL(A1;3;1)*7)+(DEEL(A1;4;1)*6)+(DEEL(A1;5;1)*5)+(DEEL(A1;6;1)*4)+(DEEL(A1;7;1)*3)+(DEEL(A1;8;1)*2)+RECHTS(A1)*-1;11);999)=0)
Bovenstaande formule kan je gebruiken als logische test in een ALS-functie; de Als functie voor het controleren van een BSN ziet er dan als volgt uit.
=ALS(EN(LENGTE(A1)=9;ISGETAL(A1*1);ALS.FOUT(REST((DEEL(A1;1;1)*9)+(DEEL(A1;2;1)*8)+(DEEL(A1;3;1)*7)+(DEEL(A1;4;1)*6)+(DEEL(A1;5;1)*5)+(DEEL(A1;6;1)*4)+(DEEL(A1;7;1)*3)+(DEEL(A1;8;1)*2)+RECHTS(A1)*-1;11);999)=0);"";"BSN niet correct")
Excel formule voor het kleuren van foute burgerservicenummers.
Als je met Voorwaardelijke Opmaak fout ingevoerde nummers een kleur wil geven, dan moet de formule juist bepalen of een burgerservicenummer fout is.
De bovenstaande formule is hierop eenvoudig aan te passen door het gebruik van de NIET-Functie.
=NIET(EN(LENGTE(A1)=9;ISGETAL(A1*1); ALS.FOUT(REST((DEEL(A1;1;1)*9)+(DEEL(A1;2;1)*8)+(DEEL(A1;3;1)*7)+(DEEL(A1;4;1)*6)+(DEEL(A1;5;1)*5)+(DEEL(A1;6;1)*4)+(DEEL(A1;7;1)*3)+(DEEL(A1;8;1)*2)+RECHTS(A1)*-1;11);999)=0))
Je Excel formule controleren.
Voor testdoeleinden kan je via http://testbestanden.nl/lijst_met_geldige_bsnnummers.php een lijst ophalen van 10 technisch correcte BSN-nummers.
Ben je niet bekend met voorwaardelijke opmaak of met gegevensvalidatie?
Wellicht is de Cursus Excel Functies en Macro’s dan interessant voor je.
BSN-nummer begint soms met een 0.
Een regelmatig voorkomende vraag is: De controle op BSN gaat fout als deze begint met een nul.
Bij een standaard celeigenschap wordt het BSN als een getal beschouwd; hierbij worden de linker nullen automatisch verwijderd. De speciale celeigenschap Sofi-nummer lijkt hiervoor de oplossing, maar is dat niet; de interne opslag is nog steeds een getal zonder voorloopnullen!
De voorloopnullen blijven alleen staan bij de celeigenschap Tekst.
Heel veel succes er mee!
Hans van Dijk.
Heeft dit artikel je geholpen? Deel deze pagina dan via de social media buttons onder aan deze pagina. De kans is groot dat andere mensen binnen je netwerk er ook wat aan hebben. Alvast bedankt!