Wie sortiert man Adressen in Excel nach Straßenname und -nummer?
Wenn Sie eine Liste von Adressen in Excel verwalten, müssen Sie diese oft nach Straßenname oder Hausnummer sortieren – etwa um Kunden aus derselben Straße zu gruppieren oder Lieferungen in der Reihenfolge steigender Hausnummern abzuwickeln. In solchen Fällen ist eine präzise Sortierung nach diesen Adressbestandteilen unerlässlich. Da jedoch typische Adressformate Straßenname und Hausnummer in einer einzigen Zelle kombinieren, führt eine einfache Sortierung meist nicht zu den gewünschten Ergebnissen. In diesem Artikel stellen wir praktische Methoden vor, mit denen Sie Adressen in Excel effizient nach Straßenname oder Hausnummer sortieren können. Wir erläutern zudem die jeweiligen Vorteile, passende Anwendungsszenarien, bieten Lösungen zur Fehlerbehebung und zeigen alternative Ansätze für unterschiedliche Anwenderbedürfnisse.
Adressen nach Straßenname mithilfe einer Hilfsspalte in Excel sortieren
Adressen nach Hausnummer mithilfe einer Hilfsspalte in Excel sortieren
Adressen mithilfe von VBA automatisch nach Straßenname oder -nummer extrahieren und sortieren
Adressen nach Straßenname oder -nummer mit Power Query sortieren (ohne Hilfsspalten)
Adressen nach Straßenname mithilfe einer Hilfsspalte in Excel sortieren
Um Adressen nach Straßenname in Excel zu sortieren, extrahieren Sie zunächst einfach die reinen Straßennamen in eine Hilfsspalte. Dieser Ansatz ist unkompliziert und funktioniert besonders gut bei konsistentem Adressformat – beispielsweise „123 Apple St“. Ideal für schnelle Projekte oder einfache Adresslisten.
1. Wählen Sie eine leere Spalte neben Ihrer Adressliste aus, und geben Sie in der ersten Zelle dieser Hilfsspalte die folgende Formel ein, um den Straßennamen zu extrahieren:
=MID(A1,FIND(" ",A1)+1,255)(Hier bezieht sich A1 auf die Zelle oberhalb Ihrer Adressdaten – passen Sie dies entsprechend an, falls Ihre Daten an einer anderen Position beginnen.)
Nach Eingabe der Formel drücken Sie Enter und ziehen dann am Ausfüllkästchen nach unten, um die Formel auf alle Zeilen Ihres Adressbereichs anzuwenden. Diese Formel sucht das erste Leerzeichen in jeder Adresse und gibt alles danach zurück – also den Straßennamen und gegebenenfalls einen Zusatz. Stellen Sie sicher, dass Ihre Adressen einheitlich strukturiert sind; andernfalls funktioniert die Aufteilung möglicherweise nicht wie erwartet.
2. Markieren Sie die gesamte Hilfsspalte (die Spalte mit den extrahierten Straßennamen), wechseln Sie dann zur Daten-Registerkarte und klicken Sie auf Aufsteigend. Dadurch werden die Straßennamen in aufsteigender (alphabetischer) Reihenfolge sortiert.
3. Wählen Sie im erscheinenden Dialogfeld Sortierwarnung die Option Auswahl erweitern, um sicherzustellen, dass die vollständigen Adressinformationen beim Sortieren zusammenbleiben.
4. Klicken Sie auf Sortieren. Ihre Adressliste wird nun entsprechend der Straßennamen neu angeordnet, sodass ähnliche Straßen direkt beieinander erscheinen.
Hinweis: Diese Methode funktioniert am besten bei standardisierten Adressformaten. Enthalten Ihre Adresszellen unregelmäßige Muster oder mehrere Leerzeichen vor dem Straßennamen, muss die Formel möglicherweise angepasst werden. Prüfen Sie nach der Anwendung stets einige Ergebnisse auf ihre Richtigkeit.
Vorteile: Einfach und ohne zusätzliche Tools nutzbar.
Nachteile: Erfordert ein konsistentes Format; bei abweichendem Adressformat entsteht zusätzlicher Aufwand.
Adressen nach Hausnummer mithilfe einer Hilfsspalte in Excel sortieren
Wenn Sie eine Liste von Adressen nach der numerischen Hausnummer sortieren möchten – etwa um eine Lieferreihenfolge festzulegen oder benachbarte Adressen zu identifizieren –, ist es ganz einfach, diese Zahlen zu extrahieren und für die Sortierung zu nutzen. Das funktioniert auch dann effektiv, wenn sich die Adressen auf verschiedenen Straßen befinden.
1. Geben Sie in einer leeren Zelle neben Ihrer Adressliste die folgende Formel ein, um die Hausnummer zu extrahieren:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(Hierbei ist A1 die erste Adresse in Ihrer Liste – passen Sie dies gegebenenfalls an.) Drücken Sie nach der Eingabe Enter. Diese Formel sucht das erste Leerzeichen und gibt alle Zeichen davor zurück, wobei sie diese in einen numerischen Wert umwandelt. Wenn Ihre Adressen mit führenden Ziffern als Hausnummern beginnen, funktioniert die Formel einwandfrei. Ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf den Rest Ihrer Liste anzuwenden.
2. Markieren Sie die soeben erstellte Hilfsspalte, wechseln Sie zur Daten-Registerkarte und klicken Sie auf Aufsteigend(oder in neueren Excel-Versionen auf)Sortieren: Kleinste zu Größte).
3. Wählen Sie im Dialogfeld Sortierwarnung die Option Auswahl erweitern, um ganze Zeilen zu sortieren.
4. Klicken Sie auf Sortieren, um die Änderung anzuwenden. Ihre Adressen sind nun nach der extrahierten Hausnummer sortiert.
Tipp:Falls Sie die Hausnummer als Text beibehalten möchten oder keine numerische Sortierung benötigen, können Sie alternativ folgende Formel verwenden:
=LEFT(A1,FIND(" ",A1)-1)Diese Version extrahiert die Hausnummer als Textstring.
Hinweis: Falls Adressen mit Wörtern statt mit Zahlen beginnen (z. B. „Main Street5“), funktionieren diese Formeln nicht wie vorgesehen. Prüfen Sie Ihre Adressdaten daher sorgfältig, bevor Sie die Formel verwenden.
Vorteile: Schnell und benutzerfreundlich, wenn das Adressformat einfach ist.
Nachteile: Verarbeitet keine Adressen, bei denen Namen oder Zusätze der Hausnummer vorangehen, sowie keine Adressen mit mehreren Zahlen.
VBA-Code – Automatisches Sortieren von Adressen durch Extraktion von Straßenname/-nummer und Sortierung der Liste mittels Makro
Für Benutzer, die mit umfangreicheren, komplexeren Adresslisten arbeiten oder deren Daten variable Adressstrukturen aufweisen, bietet die Automatisierung des Sortiervorgangs per VBA eine äußerst effektive Lösung. Mit VBA extrahieren Sie im Handumdrehen Straßennamen oder -nummern und sortieren Ihre Adressliste automatisch – manuelle Schritte werden so auf ein Minimum reduziert. Diese Methode ist ideal, wenn Sie regelmäßig sortieren müssen oder die Sortierung nahtlos in Ihren Arbeitsablauf integrieren möchten.
Hinweis: Dieses VBA-Makro extrahiert den Straßennamen (den Teil nach dem ersten Leerzeichen) aus jeder Adresse in Spalte A und sortiert die gesamte Liste anhand dieser Namen. Mit geringfügigen Anpassungen funktioniert es auch zum Extrahieren und Sortieren nach Hausnummer.
1. Klicken Sie auf die Entwickler-Registerkarte > Visual Basic. Klicken Sie im erscheinenden Fenster auf Einfügen > Modul, und fügen Sie den folgenden VBA-Code in das Modulfenster ein:
Sub SortAddressesByStreetName()
Dim ws As Worksheet
Dim lastRow As Long
Dim tempCol As Long
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
tempCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column + 1
' Create helper column with street names
For i = 1 To lastRow
ws.Cells(i, tempCol).Value = Trim(Mid(ws.Cells(i, 1).Value, InStr(ws.Cells(i, 1).Value, " ") + 1))
Next i
' Sort the whole data range by the helper column
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range(ws.Cells(1, tempCol), ws.Cells(lastRow, tempCol)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
.SetRange ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, tempCol))
.Header = xlNo
.Apply
End With
' Delete helper column
ws.Columns(tempCol).Delete
End Sub2. Um den Code auszuführen, klicken Sie bei aktiver Adressliste auf die Schaltfläche 
oder drücken Sie F5. Ihre Adressliste in Spalte A wird nun alphabetisch nach Straßenname sortiert.
Diese Version extrahiert ausschließlich die Zahl vor dem ersten Leerzeichen und sortiert sie in numerischer Reihenfolge.
Fehlerbehebung:
– Stellen Sie sicher, dass sich die Adressen in Spalte A befinden, oder passen Sie den Code entsprechend an Ihren Datenspeicherort an.
– Falls Ihre Daten eine Überschrift enthalten, müssen Sie ggf. Header = xlYes anpassen, um zu verhindern, dass die Überschriftszeile sortiert wird.
– Erstellen Sie stets eine Sicherungskopie, bevor Sie Massen-VBA-Code ausführen.
Vorteile: Keine Hilfsspalten erforderlich – ideal für große Datensätze oder wiederholte Sortiervorgänge.
Nachteile: Die Ersteinrichtung erfordert Makroberechtigungen und Grundkenntnisse in VBA.
Weitere integrierte Excel-Methoden – Verwenden Sie Power Query, um Adressspalten aufzuteilen und direkt in Power Query ohne Hilfsspalten zu sortieren
Power Query, verfügbar in modernen Excel-Versionen (Excel 2016 und höher sowie Microsoft 365), bietet eine flexible, formelfreie Möglichkeit, Adressen in Bestandteile wie Hausnummer und Straßenname zu zerlegen. Diese Lösung eignet sich ideal, wenn Sie auf Formeln und Hilfsspalten verzichten möchten oder wenn Ihre Adressen unterschiedliche Formate aufweisen, die sich mit einfachen Formeln nicht effizient verarbeiten lassen. Power Query speichert zudem alle durchgeführten Schritte, sodass Sie diese bei wachsenden Datenmengen jederzeit aktualisieren können.
1. Markieren Sie Ihre Adressdaten, wechseln Sie zur Daten-Registerkarte und wählen Sie dann Aus Tabelle/Bereich (erstellen Sie eine Tabelle, falls dazu aufgefordert wird).
2. Wählen Sie im Power Query-Fenster Ihre Adressspalte aus und klicken Sie anschließend auf Spalte teilen > Nach Trennzeichen. Wählen Sie Leerzeichen als Trennzeichen , und wählen Sie das erste links stehende Trennzeichen für den Typ Teilen bei.
3. Dadurch wird die Adresse in zwei Spalten geteilt: Hausnummer und verbleibender Straßenname/Adresse. Benennen Sie die neuen Spalten gegebenenfalls um.
4. Zum Sortieren klicken Sie auf den Pfeil in der Spaltenüberschrift entweder der Spalte „Straßenname“ oder „Hausnummer“ und wählen Aufsteigend sortieren oder Absteigend sortieren.
5. Klicken Sie auf Schließen & Laden, um die sortierten Ergebnisse wieder in Ihr Arbeitsblatt einzufügen.
Zusätzliche Tipps:
- Wenn Ihr Adressformat nicht einheitlich ist, können Sie Spalten in Power Query mithilfe benutzerdefinierter Aufteilungen oder Transformationen gezielt anpassen.
- Power-Query-Schritte werden automatisch aufgezeichnet – so aktualisieren Sie Ihre Daten mühelos, sobald sich die Quelle ändert.
- Diese Methode verändert Ihre Originaldaten nicht und erhöht so die Sicherheit Ihrer Originaldatensätze.
Vorteile: Keine dauerhafte Änderung Ihres Arbeitsblatts; robust bei komplexen Adressmustern; keine Formeln zu verwalten.
Nachteile: Erfordert Excel 2016 oder neuer; die Oberfläche könnte für neue Benutzer ungewohnt sein.
Zusammenfassung und Fehlerbehebungstipps:
– Prüfen Sie vor dem Einsatz von Formeln oder VBA die Konsistenz Ihres Adressformats.
– Überprüfen Sie stets eine Vorschau der Sortierungsergebnisse, um deren Richtigkeit zu bestätigen – besonders nach der Verwendung von Hilfsspalten oder Code.
– Passen Sie bei Daten mit unerwarteter Struktur (z. B. fehlende Nummern oder Straßennamen am Ende) Ihre Formeln entsprechend an oder nutzen Sie Power Query für eine robustere Trennung.
– Erstellen Sie regelmäßig Sicherungskopien, bevor Sie VBA oder erweiterte Datenwerkzeuge verwenden, um versehentlichen Datenverlust zu vermeiden.
– Wählen Sie die Lösung (Formeln, VBA oder Power Query), die am besten zu Ihrem Datenvolumen, Ihrer Excel-Version und Ihrem Komfortgrad mit dem jeweiligen Werkzeug passt.
– Sollten Sie unsicher sein, welche Methode am besten geeignet ist, bietet Power Query oft die größte Flexibilität und ist am sichersten für zerstörungsfreie Bearbeitung.
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