Skip to the content

Functie voor het splitsen van een adres in Excel

Als je een nieuwe website aanschaft of een nieuw financieel softwarepakket dan is de conversie van data altijd een leuke klus die je veel tijd kan kosten. In dit blog leg ik uit hoe je een functie maakt in VBA voor Excel (of Ms Access) waarmee je een adres op kunt splitsen in een straatnaam, huisnummer, postcode en plaatsnaam.

De in dit blog gebruikte methoden zijn geschikt voor Nederlandse adressen en dienen ter illustratie voor het werken met het splitsen van stringwaarden.

Maak in je Excel sheet eerst een module aan. Als je niet weet hoe dit moet lees dan eerst het blog over het maken van een functie in VBA voor Excel.

Voor de leesbaarheid heb ik de module Stringfuncties genoemd maar je bent helemaal vrij in het benoemen van de module. Standaard is de naam van een module [module1].

Wist je dat we adressen graag voor je splitsen en controleren?. Je levert de adressen aan in Excel en je krijgt de adressen gesplitst, gecontroleerd op schrijfwijze en met GPS posties terug. Al vanaf 3 cent per adres. Lees hier meer...

Positie huisnummer bepalen

We gaan het adres opsplitsen volgens het normale formaat van een Nederlands adres. Dit betekent dat we in chronologische volgorde de volgende gegevens opvragen uit de string

  • Straatnaam
  • Huisnummer + toevoeging
  • Postcode
  • Woonplaats

 

Private Function Positiehuisnummer(strAdres) As Integer
    'Het huisnummer start met de eerste numerieke waarde in de reeks
    Dim intLengte, intStart, intPositie As Integer
    Dim strWaarde As String
    'Bepaal de lengte van de volledige string
    intLengte = Strings.Len(strAdres)
    'Nu lussen (1 voor 1 alle tekens doorlopen) we de reeks totdat het eerste numerieke teken wordt gevonden
    For intStart = 1 To intLengte
        strWaarde = (Strings.Mid(strAdres, intStart, 1))
        If IsNumeric(strWaarde) Then
            'Gevonden dit is de positie van het huisnummer
            intPositie = intStart - 1
            Exit For
        End If
    Next
    Positiehuisnummer = intPositie
End Function

 

Met de functie die we hierboven hebben aangemaakt kunnen we nu de startpositie van het huisnummer bepalen. Bijvoorbeeld: =Positiehuisnummer(A2)

Reinder.eu | positie huisnummer Excel VBA

 

In bovenstaande image zie je ook het veld scheidingsteken. Als de waarden worden gescheiden door een spatie geef in dit veld dan ook een spatie mee. Excel zal dit zelf aanpassen naar een ' en spatie om aan te geven dat het veld een tekstwaarde bevat.

Met dit scheidingsteken bepalen we in de functie Postcode de volgende spatie in de reeks gerekend vanaf het huisnummer.

De functies uitgelegd

De functie Mid geeft een enkele of een reeks tekens terug uit een string waarde.

Stel de string waarde is Zeilmakersstraat 31J en we willen weten wat de positie is van de eerste numerieke waarde in deze string dan lussen we ieder karakter.

De Mid functie zal dan bij positie 17 een 3, eerste positie van een numerieke waarde in de string, terug geven.

Strings.Mid(Stringwaarde, vanaf welke positie zoeken, Hoeveel tekens zoeken)

Strings.Mid("Zeilmakersstraat 31J", 17, 1)

Private Function PositiePostcode(strAdres As String, intStart As Integer, strScheidingsteken As String) As Integer
    'Zoek nu naar de volgende numerieke reeks
    Dim intLengte, intPositie, intHuisnummerPositie As Integer
    Dim strWaarde, stringVanafHuisnummer As String
    
    intHuisnummerPositie = intStart
    stringVanafHuisnummer = (Strings.Trim((Strings.Right(strAdres, Strings.Len(strAdres) - intStart))))
    
    'Zoeken naar laatste positie in huisnummer
    intLengte = Strings.Len(stringVanafHuisnummer)
    For intStart = 1 To intLengte
        strWaarde = (Strings.Mid(stringVanafHuisnummer, intStart, 1))
        If Strings.InStr(1, strWaarde, strScheidingsteken, vbTextCompare) > 0 Then
            intPositie = intStart - 1
            Exit For
        End If
    Next
    
    'Nu volgende numerieke reeks bepalen
    intLengte = Strings.Len(stringVanafHuisnummer)
    For intEnd = intStart To intLengte
        strWaarde = (Strings.Mid(stringVanafHuisnummer, intEnd, 1))
        If IsNumeric(strWaarde) Then
            intPositie = intEnd - 1
            Exit For
        End If
    Next
    
    PositiePostcode = intHuisnummerPositie + intPositie
    
End Function

Het bepalen van de straatnaam is nu redelijk eenvoudig. Tel het aantal posities van het adres totdat het huisnummer begint. Verwijder met de functie Trim de spaties aan het begin en het einde van de straatnaam.

Private Function Straatnaam(strAdres As String, intLaatstePos As Integer) As String
    Straatnaam = Strings.Trim(Strings.Left(strAdres, intLaatstePos))
End Function

Ook het bepalen van het huisnummer is nu redelijk eenvoudig. De begin positie weten we door de functie Positiehuisnummer en de eindpositie is gelijk aan de waarde uit de functie PositiePostcode.

Alles in de range staat gelijk aan het huisnummer.

Private Function Huisnummer(strAdres As String, intStart As Integer, intEind As Integer) As String
    Huisnummer = Strings.Trim(Strings.Mid(strAdres, intStart, intEind - intStart))
End Function

Het bepalen van de postcode vergt een extra handeling. Als we ervan uitgaan dat een Nederlandse postcode altijd 6 tekens bevat dan zullen we vanaf de begin positie van de postcode eerst alle spaties moeten wissen en vervolgens de 6 tekens uit het begin van de string terug moeten geven.

Private Function Postcode(strAdres As String, intStart As Integer) As String
    Dim strWaarde As String
    strWaarde = Strings.Right(strAdres, Strings.Len(strAdres) - intStart)
    
    'Nu alle spaties wissen van de waarde
    strWaarde = SpatiesWissen(strWaarde)
    
    'Geef de eerste 6 posities terug
    Postcode = Strings.Left(strWaarde, 6)
    
End Function

Voor het bepalen van de woonplaats moeten we vanaf de begin positie van de postcode eerst bepalen wat de twee letters zijn in de postcode. Dit is nodig omdat er immers spaties kunnen bestaan tussen de cijfers en de letters van de postcode.

Aan de hand van deze twee letters kunnen we nu de eindpositie van de postcode bepalen en de woonplaats filteren uit het adres

Private Function Woonplaats(strAdres As String, intStart As Integer) As String

    Dim strWaarde, strPostcodeletters  As String
    Dim intPostcode As Integer
    Dim strWaarde1 As String
    strWaarde1 = Strings.Right(strAdres, Strings.Len(strAdres) - intStart)
    
    'Nu alle spaties wissen van de waarde
    strWaarde1 = SpatiesWissen(strWaarde1)
    'Bepaal de laatste 2 letters van de postcode
    strPostcodeletters = ""
    For intPos = 1 To Strings.Len(strWaarde1)
        strWaarde = (Strings.Mid(strWaarde1, intPos, 1))
        If Strings.Len(strPostcodeletters) = 2 Then
            Exit For
        End If
        If Not IsNumeric(strWaarde) Then
            strPostcodeletters = strPostcodeletters & strWaarde
        End If
    Next
    'Zoek nu de positie van de postcode letters
    For intPos = intStart To Strings.Len(strAdres)
        strWaarde = (Strings.Mid(strAdres, intPos, 2))
        If InStr(1, strWaarde, strPostcodeletters, vbTextCompare) > 0 Then
            intPostcode = intPos + 1
            Exit For
        End If
    Next
    
    Woonplaats = Strings.Trim(Strings.Right(strAdres, Strings.Len(strAdres) - (intPostcode)))
    
End Function

Public Function SpatiesWissen(strS As String) As String
  Dim intI
  
  strS = Strings.Trim(strS)
  intI = InStr(1, strS, " ", vbTextCompare)
  While intI <> 0
    strS = Strings.Left(strS, intI - 1) & Strings.Mid(strS, intI + 1)
    intI = InStr(1, strS, " ", vbTextCompare)
  Wend

  SpatiesWissen = strS
End Function
Public Function ScheidingstekensTellen(strS As String, strScheidingsteken As String) As Integer
    Dim intI, intR
    intR = 0
    strS = Strings.Trim(strS)
    intI = InStr(1, strS, strScheidingsteken, vbTextCompare)
    While intI <> 0
        strS = Strings.Left(strS, intI - 1) & Strings.Mid(strS, intI + 1)
        intI = InStr(1, strS, strScheidingsteken, vbTextCompare)
        intR = intR + 1
    Wend
  
    ScheidingstekensTellen = intR
End Function
Public Function AlleSpatiesWissen(strS As String) As String
  Dim intI
  
  strS = Strings.Trim(strS)
  intI = InStr(1, strS, " ", vbTextCompare)
  While intI <> 0
    strS = Strings.Left(strS, intI - 1) & Strings.Mid(strS, intI + 1)
    intI = InStr(1, strS, " ", vbTextCompare)
  Wend

  AlleSpatiesWissen = strS
End Function

Download hier het voorbeeld bestand

Download hier het Excel document met alle functies

Download - File size: 33KB