Hoe postcode uit adreslijst in Excel extraheren?
Bij het beheren van klantgegevens in Excel is het gebruikelijk om een lijst met complete adressen te hebben waarbij elke invoer zowel straatdetails als een postcode (postcode) bevat. Als je de postcodes uit deze adressen moet isoleren en extraheren - bijvoorbeeld voor mailinglijsten, regionale analyse of gegevensopmaak - kan het handmatig scheiden ervan één voor één tijdrovend en foutgevoelig zijn, vooral bij grote datasets. Gelukkig biedt Excel praktische manieren om meerdere postcodes tegelijk snel en efficiënt te extraheren zonder handmatige inspanning. Dit artikel biedt stap-voor-stap 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 een door de gebruiker gedefinieerde functie in Excel
Postcode extraheren met formule in Excel
In veel gangbare adreslijsten bevindt de postcode zich aan het einde van de adresstring en is niet langer dan 8 tekens. Deze methode is het meest geschikt wanneer alle adressen in uw lijst een consistente indeling volgen en de postcode verschijnt als het laatste element van elk adres, gescheiden door spaties.
Om snel de postcode te extraheren met behulp van een formule, volg je deze stappen:
1. Selecteer een lege cel waar je wilt dat de postcode verschijnt (bijvoorbeeld B1 als je adres in A1 staat). 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 van het adres in A1 wordt weergegeven in de geselecteerde cel.
3. Om deze formule toe te passen op andere adressen, selecteer de cel met de formule, sleep het vulhandvat omlaag langs de kolom om alle adresrijen te bestrijken, en Excel zal automatisch de postcode voor elk adres extraheren.
Tip: Deze formule gaat ervan uit dat de postcode het laatste item is na de laatste spatie in de adresstring. Als uw adressen anders zijn gestructureerd, zoals postcodes die in het midden verschijnen, of gescheiden door komma's, dan kan deze formule geen correct resultaat opleveren. Om deze methode uit te breiden voor internationale postcodes of aangepaste indelingen, heb je mogelijk een andere of meer geavanceerde formulebenadering nodig, of probeer de onderstaande VBA-oplossing.
Postcode extraheren met een door de gebruiker gedefinieerde functie in Excel
Als uw adresgegevens complexer, inconsistent of postcodes in verschillende formaten en posities binnen de string bevatten zoals in de volgende schermopname wordt getoond, kan een VBA-macro flexibele extractiemogelijkheden bieden. Deze geautomatiseerde methode verwerkt snel een hele kolom met adressen om alleen de postcodes te isoleren en terug te geven, wat aanzienlijk tijd bespaart en handmatige inspanningen minimaliseert.
1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster 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 je de code hebt geplakt, kies je in het venster van de VBA-editor Tools > References. Zie screenshot:
4. Vink in het dialoogvenster References Microsoft VBScript Regular Expressions 5.5 aan en klik op OK.
5. Ga terug naar het werkblad en voer deze formule in: =ExtractPostcode(A2), sleep het vulhandvat naar beneden naar andere cellen. Alle postcodes worden tegelijk weergegeven, zie screenshot:
Tip: Met deze code kun je automatisch postcodes uit elke land of regio in Excel extraheren in slechts enkele seconden; door eenvoudig de reguliere expressie aan te passen aan de postcoderules van je doelgebied, kun je snel aanpassen aan verschillende formaten - zoals het VK’s “SW1A 1AA,” de VS’ “12345-6789,” of China’s “100000” - wat de efficiëntie van je gegevensopschoning en analyse enorm verhoogt.
Gerelateerde artikelen:
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen