Wie extrahiert man Postleitzahlen aus einer Adressliste in Excel?

Bei der Verwaltung von Kundendaten in Excel hat man oft eine Liste vollständiger Adressen, bei denen jeder Eintrag sowohl Straßeninformationen als auch eine Postleitzahl (PLZ) enthält. Wenn Sie nur die Postleitzahlen aus diesen Adressen isolieren und extrahieren müssen – möglicherweise für Mailinglisten, regionale Analysen oder zur Datenformatierung – kann das manuelle Trennen dieser Zahlen mühsam und fehleranfällig sein, insbesondere bei großen Datensätzen. Glücklicherweise bietet Excel praktische Methoden, um mehrere Postleitzahlen auf einmal schnell und effizient zu extrahieren, ohne manuellen Aufwand. Dieser Artikel liefert Schritt-für-Schritt-Anweisungen zur Extraktion von Postleitzahlen aus Adressen mit einer Excel-Formel und 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 befindet sich die Postleitzahl am Ende der Adresszeichenfolge und ist nicht länger als 8 Zeichen. Diese Methode ist am besten geeignet, wenn alle Adressen in Ihrer Liste einem einheitlichen Format folgen und die Postleitzahl als letztes Element jeder Adresse erscheint, getrennt durch Leerzeichen.

Um die Postleitzahl schnell mit einer Formel zu extrahieren, folgen Sie diesen Schritten:

1. Wählen Sie eine leere Zelle aus, in der die Postleitzahl erscheinen soll (z. B. B1, wenn Ihre Adresse in A1 steht). 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 andere Adressen anzuwenden, wählen Sie die Zelle mit der Formel aus, ziehen Sie den Ausfüllknauf nach unten entlang der Spalte, um alle Adresszeilen abzudecken, und Excel extrahiert automatisch die Postleitzahl für jede Adresse.

Tipp: Diese Formel geht davon aus, dass die Postleitzahl das letzte Element nach dem letzten Leerzeichen in der Adresszeichenfolge ist. Wenn Ihre Adressen anders strukturiert sind, wie z. B. Postleitzahlen in der Mitte oder durch Kommas getrennt, gibt diese Formel möglicherweise nicht das richtige Ergebnis zurück. Um diese Methode für internationale Postleitzahlen oder benutzerdefinierte Formate zu erweitern, können Sie eine andere oder fortgeschrittene Formel verwenden oder die unten stehende VBA-Lösung ausprobieren.

Postleitzahl mit benutzerdefinierter Funktion in Excel extrahieren

Wenn Ihre Adressdaten komplexer, inkonsistent oder mit Postleitzahlen in verschiedenen Formaten und Positionen innerhalb der Zeichenfolge sind, wie im folgenden Screenshot gezeigt, kann ein VBA-Makro flexiblere Extraktionsmöglichkeiten bieten. Diese automatisierte Methode verarbeitet schnell eine ganze Spalte von Adressen, um nur die Postleitzahlen zu isolieren und zurückzugeben, wodurch viel Zeit gespart und der manuelle Aufwand minimiert wird.

1. Drücken Sie Alt + F11, um das Microsoft Visual Basic for Applications-Fenster zu öffnen.

2. Klicken Sie im VBA-Fenster auf Einfügen > Modul, um ein neues Modul zu erstellen. Kopieren und fügen Sie den folgenden VBA-Code 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 Function

3. Nach dem Einfügen des Codes wählen Sie im VBA-Editorfenster Extras > Referenzen. Siehe Screenshot:

4. Aktivieren Sie im Dialogfeld „Referenzen“ Microsoft VBScript Regular Expressions 5.5 und klicken Sie auf OK.

5. Kehren Sie zum Arbeitsblatt zurück und geben Sie diese Formel ein: =ExtractPostcode(A2), ziehen Sie den Ausfüllknauf nach unten in andere Zellen. Alle Postleitzahlen werden sofort angezeigt, siehe Screenshot:

Tipp: Mit diesem Code können Sie automatisch Postleitzahlen aus jedem Land oder jeder Region in Excel in nur wenigen Sekunden extrahieren; indem Sie einfach den regulären Ausdruck an die Postleitzahlenregeln Ihres Zielgebiets anpassen, können Sie schnell verschiedene Formate unterstützen – wie die britische „SW1A 1AA“, die amerikanische „12345-6789“ oder die chinesische „100000“ – was die Effizienz bei der Datenbereinigung und -analyse stark erhöht.

Verwandte Artikel:

• Initialen aus Namen in Excel extrahieren
• Dezimalwert aus Zeichenfolge in Excel extrahieren