Wie extrahieren Sie die Postleitzahl aus einer Adressliste in Excel?
Beim Verwalten von Kundeninformationen in Excel verfügen Sie oft über eine Liste vollständiger Adressen, bei der jeder Eintrag sowohl Straßendaten als auch eine Postleitzahl (PLZ) enthält. Wenn Sie nur die Postleitzahlen aus diesen Adressen isolieren und extrahieren möchten – etwa für E-Mail-Listen, regionale Analysen oder die Datenformatierung –, ist das manuelle Trennen einzelner Einträge äußerst zeitaufwändig und fehleranfällig, besonders bei großen Datensätzen. Zum Glück bietet Excel effiziente Möglichkeiten, mehrere Postleitzahlen gleichzeitig schnell und ohne manuellen Aufwand zu extrahieren. Dieser Artikel liefert Schritt-für-Schritt-Anleitungen zum Extrahieren von Postleitzahlen aus Adressen – mithilfe einer Excel-Formel sowie einer VBA-Makro-Lösung.
Postleitzahl mit Formel in Excel extrahieren
Postleitzahl mit benutzerdefinierter Funktion in Excel extrahieren
Postleitzahl mit Formel in Excel extrahieren
In vielen gängigen Adresslisten steht die Postleitzahl am Ende der Adresszeile und umfasst höchstens acht Zeichen. Diese Methode eignet sich ideal, wenn alle Adressen in Ihrer Liste ein einheitliches Format aufweisen und die Postleitzahl jeweils als letztes Element – durch ein Leerzeichen getrennt – erscheint.
Führen Sie die folgenden Schritte aus, um die Postleitzahl mithilfe einer Formel schnell zu extrahieren:
1. Wählen Sie eine leere Zelle aus, in der die Postleitzahl angezeigt werden soll (z. B. B1, wenn sich Ihre Adresse in A1 befindet), und geben Sie die folgende Formel ein:
=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))2. Drücken Sie die Eingabetaste. Die Postleitzahl aus der Adresse in A1 wird in der ausgewählten Zelle angezeigt.
3. Um diese Formel auf weitere Adressen anzuwenden, markieren Sie die Zelle mit der Formel und ziehen Sie das Ausfüllkästchen entlang der Spalte nach unten, bis alle Adresszeilen abgedeckt sind. Excel extrahiert dann automatisch die Postleitzahl für jede Adresse.
Tipp: Diese Formel setzt voraus, dass die Postleitzahl das letzte Element nach dem letzten Leerzeichen in der Adresszeichenfolge ist. Wenn Ihre Adressen anders strukturiert sind – etwa mit Postleitzahlen in der Mitte oder durch Kommas getrennt – liefert diese Formel möglicherweise kein korrektes Ergebnis. Um diese Methode für internationale Postleitzahlen oder benutzerdefinierte Formate zu erweitern, benötigen Sie unter Umständen einen anderen oder fortgeschritteneren Formelansatz – oder probieren Sie einfach die unten stehende VBA-Lösung aus.
Postleitzahl mit benutzerdefinierter Funktion in Excel extrahieren
Wenn Ihre Adressdaten komplexer, inkonsistent oder in unterschiedlichen Formaten und Positionen innerhalb der Zeichenfolge vorliegen – wie im folgenden Screenshot dargestellt – bietet ein VBA-Makro flexiblere Extraktionsmöglichkeiten. Diese automatisierte Lösung verarbeitet im Handumdrehen eine ganze Spalte mit Adressen, um gezielt nur die Postleitzahlen zu extrahieren und zurückzugeben – und spart so wertvolle Zeit bei minimalem manuellem Aufwand.
1. Drücken Sie Alt + F11, um das Fenster Microsoft Visual Basic for Applications zu öffnen.
2. Klicken Sie im VBA-Fenster auf Einfügen > Modul, um ein neues Modul zu erstellen. Kopieren Sie den folgenden VBA-Code, und fügen Sie ihn in das Modulfenster ein:
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. Wählen Sie im VBA-Editor-Fenster Extras > Verweise, nachdem Sie den Code eingefügt haben. Siehe Screenshot:
4. Aktivieren Sie im Dialogfeld Verweise das Kontrollkästchen Microsoft VBScript Regular Expressions 5,5, und klicken Sie auf OK.
5. Kehren Sie zum Arbeitsblatt zurück und geben Sie die folgende Formel ein: =ExtractPostcode(A2). Ziehen Sie anschließend das Ausfüllkästchen nach unten auf die gewünschten Zellen – schon erscheinen alle Postleitzahlen sofort. Siehe Screenshot:
Tipp: Mit diesem Code extrahieren Sie Postleitzahlen aus jedem Land oder jeder Region in Excel innerhalb weniger Sekunden automatisch. Passen Sie einfach den regulären Ausdruck an die Postleitzahlregeln Ihres Zielgebiets an, um verschiedene Formate blitzschnell zu verarbeiten – wie etwa das britische „SW1A 1AA“, das US-amerikanische „12345-6789“ oder das chinesische „100000“ – und steigern Sie so Ihre Effizienz bei der Datenbereinigung und -analyse erheblich.
Verwandte Artikel:
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins