Hoofdstuk 17 · 17.11
Straat, huisnummer en toevoeging uit één adreskolom halen
Adressen uit een webwinkel, ledenadministratie of oud klantenbestand staan vaak in één kolom: "Dorpsstraat 12-2". Voor verzendetiketten, een mailing of een import bij een vervoerder moeten straat, huisnummer en toevoeging apart. Met Tekst naar kolommen lukt dat niet goed, want straatnamen hebben zelf spaties. Voor- en achternaam splitsen staat in Voornaam en achternaam uit één kolom splitsen; adressen zijn lastiger. Euro-Office heeft daarvoor de functies REGEXEXTRAHEREN en REGEXVERVANGEN, die met een zoekpatroon werken.
Als voorbeeld nemen we de klantenlijst van Bloemisterij Het Zonnetje, met in kolom A:
- Koningin Julianalaan 15 a
- Dorpsstraat 12-2
- Laan 1940-1945 8B
- Kerkplein 7
- Hoofdstraat 120 bis
Stap 1: het huisnummer met toevoeging
Typ in B2:
=REGEXEXTRAHEREN(A2;"\d+\S*( ?[A-Za-z]{1,3})?$")
Het patroon tussen aanhalingstekens betekent: een of meer cijfers (\d+), daarna eventueel tekens zonder spatie (\S*, zoals -2 of B), daarna eventueel een spatie met 1 tot 3 letters (zoals " a" of " bis"), en dat helemaal aan het eind van de tekst ($). Uitkomst: 15 a, 12-2, 8B, 7 en 120 bis. De jaartallen in "Laan 1940-1945" blijven buiten schot, omdat alleen het laatste stuk van het adres telt.
Stap 2: de straat
Typ in C2:
=SPATIES.WISSEN(REGEXVERVANGEN(A2;"\d+\S*( ?[A-Za-z]{1,3})?$";""))
REGEXVERVANGEN vervangt hetzelfde stuk door niets, en SPATIES.WISSEN haalt de spatie aan het eind weg. Je houdt Koningin Julianalaan, Dorpsstraat, Laan 1940-1945 enzovoort over.
Stap 3: nummer en toevoeging los
Moeten ook het nummer en de toevoeging apart, werk dan verder met de uitkomst van stap 1 in B2:
- Alleen het nummer, in D2: =REGEXEXTRAHEREN(B2;"^\d+") (de cijfers aan het begin): 15, 12, 8, 7, 120.
- De toevoeging, in E2: =SPATIES.WISSEN(REGEXVERVANGEN(B2;"^\d+[- ]?";"")): a, 2, B, niets, bis.
Trek de formules daarna door tot de laatste rij. Of werk per kolom: selecteer bijvoorbeeld B2:B6, typ de formule en druk op Ctrl+Enter; dan staat hij in één keer in alle geselecteerde cellen.

Een adres met komma's
Staat het hele adres in één cel, zoals "Dorpsstraat 12, 1234 AB Utrecht", dan splitst =TEKST.SPLITSEN(A9;", ") het op de komma: "Dorpsstraat 12" komt in de cel zelf en "1234 AB Utrecht" in de cel ernaast. Postcode en plaats haal je daarna uit elkaar met TEKST.VOOR en TEKST.NA, die tekst nemen voor of na het zoveelste scheidingsteken. Staat "1234 AB Utrecht" in C9:
- Postcode: =TEKST.VOOR(C9;" ";2) geeft 1234 AB (alles voor de tweede spatie).
- Plaats: =TEKST.NA(C9;" ";2) geeft Utrecht.
Klaar? Maak er vaste waarden van
De gesplitste kolommen zijn formules die naar kolom A kijken. Wil je kolom A weggooien, of het bestand naar iemand sturen met een oudere Excel, zet de uitkomsten dan om naar gewone tekst: selecteer de kolommen, kopieer ze met Ctrl+C en plak ze op dezelfde plek met Ctrl+V. Onder de geplakte cellen verschijnt een knopje (Ctrl). Klik erop en kies onder Waarde voor Alleen waarde plakken.

Valkuilen
- Uitzonderingen blijven bestaan: "Postbus 12", "Hoofdstraat 3 hs", "tegenover 14". Zet een filter op de kolommen en kijk naar lege of vreemde uitkomsten voordat je de lijst gebruikt.
- Hoofdletters: het patroon [A-Za-z] neemt kleine en hoofdletters mee. Gebruik je alleen [a-z], dan valt 8B buiten de boot.
- #N/A bij REGEXEXTRAHEREN betekent: het patroon komt niet voor, bijvoorbeeld een adres zonder huisnummer.
- Andere spreadsheets: Excel kent deze REGEX-functies alleen in recente versies van Microsoft 365. Een collega met een oudere versie ziet #NAME?. Zet de uitkomsten dan eerst om naar waarden.
- Zonder REGEX kan het ook, maar beperkter: =TEKST.NA(A2;" ";-1) geeft alles na de laatste spatie. Dat werkt voor "Kerkplein 7" en "Dorpsstraat 12-2", maar bij "15 a" krijg je alleen de a.
