Postcode uit een tekst halen

[Justyna]

Wie kan mij helpen? Ik ben op zoek naar een formule, die uit een cel (waarin een volledig adres staat) alleen postcode eruit haalt ( bijvoorbeeld 1111 AA). Ik heb het geprobeerd met tekst naar kolommen te doen maar het werkt. Ik heb namelijk veel adressen met meerdere spaties. Welke opties zijn er nog meer?

 

[edmoor]

Optie 1:
Een voorbeeld document.

 

[Justyna]

voorbeeld

 

[AlexCEL]

Zo? (Excel 365)

=LET(x;--TEKST.SPLITSEN(A2;" ");y;INDEX(FILTER(x;ISGETAL(x);"");2);DEEL(A2;VIND.SPEC(y;A2);7))

Zijn een paar waarden waar dit niet goed gaat omdat de postcode geen spatie bevat, of het huisnummer verkeerd genoteerd staat, of geen postcode aanwezig is. Die zul je handmatig even moeten corrigeren.

 

[AlexCEL]

Een verbeterde versie die rekening houdt met de eerste 2 problemen zoals genoemd hierboven:

=LET(x;ALS.FOUT(--LINKS(TEKST.SPLITSEN(A2;" ");4);"");y;FILTER(x;ISGETAL(x)*(LENGTE(x)=4);"");z;DEEL(A2;VIND.SPEC(y;A2);7);ALS(LENGTE(z)=6;VERVANGEN(z;4;1;" ");z))

 

[AHulpje]

Als je geen Office 365 hebt:

 

[snb]

Alle 100 goed:

Sub M_snb()
   sn = Cells(1).CurrentRegion
   For j = 2 To UBound(sn) - 1
     For jj = 10 To Len(sn(j, 1)) - 6
       If Val(Mid(sn(j, 1), jj, 4)) > 999 Then Exit For
     Next
     sn(j, 1) = Trim(Mid(sn(j, 1), jj, 7))
     If Len(sn(j, 1)) = 6 Then sn(j, 1) = Left(sn(j, 1), 4) & " " & Right(sn(j, 1), 2)
   Next
   Cells(1, 4).Resize(UBound(sn)) = sn
End Sub

 

[emields]

Alle 100 goed:

Tot er iemand op de ottergemsesteenweg nr 1111 woont.

 

[Gtw19]

Tot er iemand op de ottergemsesteenweg nr 1111 woont.

Hey dag Emields,
die kennen we in Gent België ook de Ottergemsesteenweg .....

Mvg
Gtw

 

[JEC.]

Zelfs de Ottergemsesteenweg 1111 ontsnapt hier niet aan

Sub jec()
 Dim ar, j As Long
 ar = Cells(1).CurrentRegion
 With CreateObject("vbscript.regexp")
   .Pattern = "[0-9]{4}\s?[A-Z]{2}"
   For j = 1 To UBound(ar)
     If .test(ar(j, 1)) Then ar(j, 1) = .Execute(ar(j, 1))(0)
   Next
   Cells(1, 5).Resize(UBound(ar)) = ar
 End With
End Sub

 

[JEC.]

En deze zal een spatie toevoegen in de output, indien de postcode geen spatie bevat.

Sub jec()
 Dim ar, j As Long
 ar = Cells(1).CurrentRegion
 With CreateObject("vbscript.regexp")
   .Pattern = "(.*?)([0-9]{4})\s?([A-Z]{2})(.*)"
   For j = 1 To UBound(ar)
      ar(j, 1) = .Replace(ar(j, 1), "$2 $3")
   Next
   Cells(1, 5).Resize(UBound(ar)) = ar
 End With
End Sub

 

[snb]

@Emiel,

Had je natuurlijk zelf wel even kunnen aanpassen.

Sub M_snb()
   sn = Cells(1).CurrentRegion
   For j = 2 To UBound(sn) - 1
     For jj = Len(sn(j, 1)) - 6 to 10 step -1

       If Val(Mid(sn(j, 1), jj, 4)) > 999 Then Exit For
     Next
     sn(j, 1) = Trim(Mid(sn(j, 1), jj, 7))
     If Len(sn(j, 1)) = 6 Then sn(j, 1) = Left(sn(j, 1), 4) & " " & Right(sn(j, 1), 2)
   Next
   Cells(1, 4).Resize(UBound(sn)) = sn
End Sub

 

[sylvester-ponte]

Function Postcode(T As String)
For n = 1 To Len(T) - 6
tt = Mid(T, n, 7)
If tt Like "####*??" Then Postcode = tt: Exit Function
Next
End Function


hoe zet ik die css of code aan

 

[Justyna]

Bedankt voor jullie hulp