Hoe haal ik de postcode uit een adreslijst in Excel?
Bij het beheren van klantgegevens in Excel beschikt u vaak over een lijst met volledige adressen, waarin elk item zowel straatgegevens als een postcode (postcode of zip code) bevat. Als u alleen de postcodes uit deze adressen wilt isoleren en extraheren – bijvoorbeeld voor een e-maillijst, regionale analyse of gegevensopmaak – is handmatig scheiden tijdrovend en foutgevoelig, vooral bij grote datasets. Gelukkig biedt Excel slimme oplossingen om snel en efficiënt meerdere postcodes tegelijk te extraheren, zonder handmatige inspanning. Dit artikel bevat stapsgewijze instructies voor het extraheren van postcodes uit adressen met behulp van een Excel-formule en een VBA-macro-oplossing.
Postcode extraheren met formule in Excel
Postcode extraheren met door gebruiker gedefinieerde functie in Excel
Postcode extraheren met formule in Excel
In veel gangbare adressenlijsten staat de postcode aan het einde van de adresregel en is deze maximaal 8 tekens lang. Deze methode werkt het beste wanneer alle adressen in uw lijst een consistente opmaak hebben en de postcode als laatste onderdeel van elk adres verschijnt, gescheiden door spaties.
Volg deze stappen om de postcode snel te extraheren met een formule:
1. Selecteer een lege cel waarin u de postcode wilt weergeven (bijvoorbeeld B1 als uw adres in A1 staat) en voer de volgende formule in:
=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))2. Druk op de Enter-toets. De postcode uit het adres in A1 wordt weergegeven in de geselecteerde cel.
3. Om deze formule toe te passen op andere adressen, selecteert u de cel met de formule en sleept u de vulgreep omlaag langs de kolom totdat alle adresrijen zijn meegenomen. Excel haalt dan automatisch de postcode uit elk adres.
Tip: Deze formule gaat ervan uit dat de postcode het laatste onderdeel is na de laatste spatie in de adresreeks. Als uw adressen anders zijn opgebouwd – bijvoorbeeld met postcodes in het midden of gescheiden door komma’s – werkt deze formule mogelijk niet correct. Om deze methode aan te passen voor internationale postcodes of aangepaste formaten, hebt u mogelijk een andere of geavanceerdere formule nodig, of kunt u de onderstaande VBA-oplossing proberen.
Postcode extraheren met door gebruiker gedefinieerde functie in Excel
Als uw adresgegevens complexer, inconsistenter zijn of postcodes bevatten in verschillende formaten en posities binnen de reeks, zoals in de onderstaande afbeelding wordt getoond, kan een VBA-macro flexibeler extractiemogelijkheden bieden. Met deze geautomatiseerde methode verwerkt u snel een Gehele kolom adressen om alleen de postcodes te isoleren en weer te geven, wat aanzienlijk tijd bespaart en handmatige inspanning minimaliseert.
1. Druk op Alt + F11 om het venster Microsoft Visual Basic for Applications te openen.
2. Klik in het VBA-venster op Invoegen > Module om een nieuwe module te maken. Kopieer en plak de volgende VBA-code in het modulevenster:
Public Function ExtractPostcode(text As String) As String
Dim reg As New RegExp
Dim m As MatchCollection
reg.Pattern = "\b([A-Z]{1,2}\d{1,2}[A-Z]?\s*\d[A-Z]{2}|\d{5}(?:-\d{4})?|\d{6})\b"
reg.IgnoreCase = True
reg.Global = False
If reg.Test(text) Then
Set m = reg.Execute(text)
ExtractPostcode = m(0).Value
Else
ExtractPostcode = ""
End If
End Function3. Nadat u de code hebt geplakt, kiest u in het VBA-editorvenster Extra > Referenties. Zie afbeelding:
4. Schakel in het dialoogvenster Referenties het selectievakje Microsoft VBScript Regular Expressions 5,5 in en klik op OK.
5. Ga terug naar het werkblad en voer de formule =ExtractPostcode(A2) in. Sleep daarna de vulgreep omlaag naar andere cellen—alle postcodes worden direct weergegeven. Zie afbeelding:
Tip: Met deze code haalt u in slechts enkele seconden automatisch postcodes uit elk land of regio in Excel. Door de reguliere expressie eenvoudig aan te passen aan de postcode-regels van uw doelgebied, past u zich razendsnel aan verschillende formaten aan – zoals „SW1A 1AA” in het VK, „12345-6789” in de VS of „100000” in China – en verhoogt u uw efficiëntie bij het opschonen en analyseren van gegevens aanzienlijk.
Gerelateerde artikelen:
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins