Door gebruiker gedefinieerde functie: PostcodeNL

Dit blog gaat over hoe je een zelf gedefinieerd functie maakt en hoe je deze van informatie voorziet in het functie-overzicht. Maar dit blog gaat ook over hoe je een postcode uit een tekst kunt halen m.b.v. zo'n zelf gedefinieerde functie. En hoe je op andere manieren een postcode uit een tekst kunt halen.

Snel aanvullen: Snelle manier om postcodes uit teksten te halen (maar niet altijd betrouwbaar):

In bovenstaand voorbeeld staan in kolom A adressen op verschillende manieren vermeld. Om snel hier de postcodes uit te halen, kun je in cel B1 de postcode vermelden zoals deze in de tekst van cel A1 staat. Als je daarna cel B2 selecteert en Snel aanvullen kiest, dan worden de andere waarden automatisch gegenereerd.
De Snel Aanvullen-optie kan geactiveerd worden met de sneltoets Cntrl+E maar kan ook gevonden worden in het Start-lint > Bewerken > Doorvoeren > Snel aanvullen. Maar zoals al zichtbaar is, is de inschatting die Excel hierbij maakt niet overal correct.

Tekst naar kolommen

Als alle tekst in een komma-gescheiden-bestand (*.csv) staan, dan kan de Tekst-naar-kolommen uit het Gegevens-lint ook een snelle oplossing zijn. Maar als de verschillende onderdelen van het adres niet door een scheidingsteken worden gescheiden, dan kan onderstaande functie hulp bieden.

Door gebruiker gedefinieerde functie

Als de adressen niet altijd op dezelfde manier zijn opgebouwd, dan schieten bovenstaande mogelijkheden te kort. "Beter goed gejat dan slecht bedacht" dus ik ging op zoek op internet naar een oplossing. In een blog vond ik een soortgelijke vraag voor een postcode uit een ander land met een andere combinatie van letters en cijfers (link naar het forumbericht op Ozgrid.com staat in het bestand vermeld). Door deze iets aan te passen is het mogelijk om de Nederlandse postcode uit een tekst te halen.

In cel B1 kan met de functie =POSTCODENL(",";A1) de postcode uit de tekst gehaald worden. De functie controleert op de volgende drie opties:

• Zoek in de opgegeven cel naar een woord van vier cijfers gevolgd door een woord van twee hoofdletters
• Idem maar dan met kleine letters
• Zoek in de opgegeven cel naar een woord van zes tekens waarvan de eerste vier een cijfer zijn en de laatste twee hoofdletters
• Idem maar dan met kleine letters

Als dit gevonden is, geef dit dan weer met vier cijfers, een spatie en twee hoofdletters. En als het niet wordt gevonden, dan wordt er geen antwoord vermeld. Zo worden de postcodes uit de tekst gehaald. (En als jij een voorbeeld weet waar dit tot onjuiste informatie leidt, dan hoor ik dat graag).

In de VBA-editor is te zien hoe de functie is opgebouwd. Alleen heb je dan nog geen help-tekst bij functie zoals je deze ziet als je op de fx-knop klikt. Maar ook dat is met een macro te regelen.

Als op de fx-button wordt geklikt worden eerst de variabelen van de functie getoond met daarbij de invoervelden. Daaronder de omschrijving van wat de functie doet en daaronder de omschrijving van het geselecteerde veld. Deze teksten aan een door de gebruiker gedefinieerde functie toevoegen kan niet in de functie zelf worden ingebouwd maar met een aparte functie kan dit wel toegevoegd worden. In het Excel-bestand staat in de VBA-editor de originele versie die ik vond op myengineeringworld.net en ook een Nederlandse vertaling.

Het nadeel van deze methode is dat de functie alleen werkt in het bestand waarin de werking van de functie in macro-code is vastgelegd. Een ander die het bestand gebruikt kan dan ook deze functie gebruiken (mits de macro's ingeschakeld zijn). 
Je kunt zo'n functie ook in je Persoonlijk bestand "Personal.xlsb" opslaan zodat je het in al je bestanden kunt gebruiken. Maar als een ander deze functie niet heeft, zal de berekening tot fouten gaan leiden. Iets om dus goed over na te denken als je gebruik maakt van zelf gedefinieerde functies. 

Controle op correct ingevoerde postcodes m.b.v. gegevensvalidatie of voorwaardelijke opmaak

Vaak zullen de adresgegevens netjes worden aangeleverd waarbij straat, huisnummer, huisnummertoevoeging, postcode en woonplaats in verschillende kolommen worden weergegeven. Maar als hier handmatig gegevens aan toegevoegd moeten worden, dan kan er een fout gemaakt worden bij het invoeren van de postcode. Hoewel het niet een door de gebruiker gedefinieerd functie betreft, wil ik hier toch twee oplossingen vermelden waarmee eenvoudig gecontroleerd kan worden of de ingevoerde postcode correct is. Niet dat het controleert of de ingevoerde postcode bestaat, maar wel of de ingevoerde tekst vier cijfers en twee letters bevat, met of zonder spatie.

Bij gegevensvalidatie kan een foutmelding worden weergegeven als de postcode niet aan de juiste voorwaarden voldoet.

Selecteer de cel of de cellen waarin de postcodes staan en klik in het Gegevens-lint op Gegevensvalidatie. Kies op tabblad Instellingen voor een Aangepaste formule en vul bij de formule de volgende formule in (er van uitgaande dat in cel D2 de postcode staat):

Voor een postcode zonder spatie:
=EN(LENGTE(D2)=6;ISGETAL(WAARDE(LINKS(D2;4)));ISTEKST(RECHTS(D2;2));GELIJK(RECHTS(D2;2);HOOFDLETTERS(RECHTS(D2;2))))

Voor een postcode met een spatie:
=EN(LENGTE(D2)=7;deel(D2;5;1)=" ";ISGETAL(WAARDE(LINKS(D2;4)));ISTEKST(RECHTS(D2;2));GELIJK(RECHTS(D2;2);HOOFDLETTERS(RECHTS(D2;2))))

Vul op tabblad Foutmelding de titel en de aanvullende tekst in om de gebruiker duidelijk te maken wat er niet goed is ingevuld.

Met de vernieuwde voorwaardelijke opmaak kan ook eenvoudig een formule worden ingevoerd die de opmaak wijzigt als de ingevoerde gegevens aan (één van de) voorwaarden voldoet. Selecteer de cel of de cellen waarin de postcodes staan en klik in Start-lint op Voorwaardelijke opmaak en kies de keuze "Nieuwe regel". Hierbij wordt een scherm getoond met meerdere mogelijkheden. Kies de optie "Een formule gebruiken om te bepalen welke cellen worden opgemaakt" en vul de volgende formule in:
=OF(LENGTE(D2)<>6;ISGETAL(WAARDE(LINKS(D2;4)))=ONWAAR;ISTEKST(RECHTS(D2;2))=ONWAAR;GELIJK(RECHTS(D2;2);HOOFDLETTERS(RECHTS(D2;2)))=ONWAAR)

Kies daarna een bepaalde opmaak en als de postcode één van de genoemde fouten heeft, dan zal de opmaak worden toegepast.

Het bestand met de postcode-functie kun je HIER downloaden van Google Drive.
En is iets niet duidelijk, stuur dan een berichtje via het contactformulier van mijn website.