Wie sortiert man Adressen nach Straßennamen/Nummern in Excel?
Wenn Sie eine Liste von Adressen in Excel verwalten, ist es oft notwendig, die Daten zu organisieren oder zu analysieren, indem Sie Adressen entweder nach Straßennamen oder nach Hausnummern sortieren. Zum Beispiel, wenn Sie Kunden, die in derselben Straße wohnen, gruppieren müssen, oder Lieferungen in der Reihenfolge der Hausnummern abarbeiten müssen, ist das Sortieren nach diesen Komponenten unerlässlich. Da jedoch typische Adressformate Straßennamen und Nummern innerhalb einer einzigen Zelle mischen, führt einfaches Sortieren nicht zu den erwarteten Ergebnissen. In diesem Artikel werden wir praktische Methoden besprechen, um Adressen in Excel nach Straßennamen oder Hausnummern zu sortieren, deren Vorteile und Anwendungsszenarien analysieren und Lösungen für verschiedene Benutzeranforderungen anbieten.
Adressen mit einer Hilfsspalte nach Straßennamen sortieren
Adressen mit einer Hilfsspalte nach Hausnummern sortieren
Adressen mit VBA automatisch extrahieren und nach Straßennamen oder -nummern sortieren
Adressen nach Straßennamen oder -nummern mit Power Query sortieren (keine Hilfsspalten)
Adressen mit einer Hilfsspalte nach Straßennamen sortieren
Um Adressen in Excel nach Straßennamen zu sortieren, müssen Sie zunächst nur die Straßennamen in eine Hilfsspalte extrahieren. Dieser Ansatz ist einfach und funktioniert gut, wenn das Adressformat konsistent ist, wie z.B. "123 Apfelstraße". Es eignet sich für schnelle Projekte oder einfache Adresslisten.
1. Wählen Sie eine leere Spalte neben Ihrer Adressliste aus. Geben Sie die folgende Formel in die erste Zelle der Hilfsspalte ein, um den Straßennamen zu extrahieren:
=MID(A1,FIND(" ",A1)+1,255)(Hier bezieht sich A1 auf die oberste Zelle Ihrer Adressdaten – passen Sie dies an, falls Ihre Daten woanders beginnen.)
Nachdem Sie die Formel eingegeben haben, drücken Sie Eingabetaste und ziehen dann den Ausfüllkästchen nach unten, um die Formel auf alle Zeilen Ihres Adressbereichs anzuwenden. Diese Formel funktioniert, indem sie das erste Leerzeichen in jeder Adresse findet und dann alles danach zurückgibt – den Straßennamen und eventuelle Suffixe. Stellen Sie sicher, dass Ihre Adressen demselben Aufbau folgen; andernfalls könnte die Formel nicht wie erwartet aufteilen.
2. Markieren Sie die gesamte Hilfsspalte (die Spalte mit den extrahierten Straßennamen), gehen Sie zur Registerkarte Daten und klicken Sie auf Sortieren A bis Z. Dadurch werden die Straßennamen in aufsteigender (alphabetischer) Reihenfolge sortiert.
3. Wählen Sie im erscheinenden Dialogfeld Sortierwarnung Bereich erweitern, um sicherzustellen, dass die vollständigen Adressinformationen beim Sortieren zusammenbleiben.
4. Klicken Sie auf Sortieren. Ihre Adressliste wird nun basierend auf den Straßennamen neu geordnet, sodass ähnliche Straßen zusammen erscheinen.
Hinweis: Diese Methode funktioniert am besten mit standardisierten Adressformaten. Wenn Ihre Adresszellen unregelmäßige Muster oder mehrere Leerzeichen vor dem Straßennamen enthalten, muss die Formel möglicherweise angepasst werden. Überprüfen Sie immer einige Ergebnisse auf Richtigkeit, nachdem Sie die Formel verwendet haben.
Vorteile: Einfach, erfordert keine zusätzlichen Tools.
Nachteile: Hängt von konsistentem Format ab; Extraarbeit erforderlich, wenn das Adressformat variiert.
Adressen mit einer Hilfsspalte nach Hausnummer in Excel sortieren
Wenn Sie eine Liste von Adressen nach der numerischen Hausnummer sortieren müssen – zum Beispiel für die Zuweisung einer Lieferreihenfolge oder zur Identifizierung benachbarter Adressen – ist es einfach, die Nummer zu extrahieren und für das Sortieren zu verwenden. Dies funktioniert auch 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))(Wobei A1 die erste Adresse in Ihrer Liste ist – je nach Bedarf anpassen.) Drücken Sie nach der Eingabe die Eingabetaste. Diese Formel funktioniert, indem sie das erste Leerzeichen findet und die Zeichen davor zurückgibt, wobei sie diese in einen numerischen Wert umwandelt. Wenn Ihre Adressen führende Ziffern als Hausnummern enthalten, wird diese Formel korrekt funktionieren. Ziehen Sie dann das Ausfüllkästchen nach unten, um die Formel auf den Rest Ihrer Liste anzuwenden.
2. Wählen Sie die Hilfsspalte aus, die Sie gerade erstellt haben, gehen Sie zur Registerkarte Daten und klicken Sie auf Sortieren A bis Z (oder Sortieren kleinster zu größter für neuere Excel-Versionen).
3. Wählen Sie im Sortierwarnungsdialogfeld Bereich erweitern, um ganze Zeilen zu sortieren.
4. Klicken Sie auf Sortieren, um es anzuwenden. Ihre Adressen sind nun nach der extrahierten Hausnummer sortiert.
Tipp: Wenn Sie die Hausnummer lieber als Text behalten möchten oder keine numerische Sortierung durchführen müssen, können Sie auch Folgendes verwenden:
=LEFT(A1,FIND(" ",A1)-1)Diese Version extrahiert die Zahl als Textzeichenfolge.
Vorsichtsmaßnahmen: Wenn Adressen mit Worten statt mit Zahlen beginnen (z.B. "Hauptstraße5"), funktionieren diese Formeln nicht wie vorgesehen. Überprüfen Sie Ihre Adressdaten vor der Verwendung der Formel.
Vorteile: Schnell und benutzerfreundlich, wenn das Adressformat einfach ist.
Nachteile: Behandelt Adressen mit Namen/Suffixen vor der Nummer oder Adressen mit mehreren Zahlen nicht.
VBA-Code – Automatisches Sortieren von Adressen durch Extrahieren von Straßennamen/Nummern und Sortieren der Liste mit einem Makro
Für diejenigen, die mit größeren, komplexeren Adresslisten arbeiten oder deren Daten variable Adressstrukturen umfassen, kann die Automatisierung des Sortiervorgangs mithilfe von VBA sehr effektiv sein. VBA ermöglicht es Ihnen, schnell Straßennamen oder -nummern zu extrahieren, Ihre Adressliste automatisch zu sortieren und manuelle Schritte zu minimieren. Diese Lösung ist geeignet, wenn Sie regelmäßig sortieren müssen oder das Sortieren in einen Workflow 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 basierend auf diesen Namen. Es funktioniert auch für das Extrahieren und Sortieren nach Hausnummer mit geringfügigen Anpassungen.
1. Klicken Sie auf Entwicklertools > Visual Basic. Im erscheinenden Fenster klicken Sie auf Einfügen > Modul und fügen 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 Sub2Um den Code auszuführen, mit der aktiven Adressliste klicken Sie auf die 
Schaltfläche oder drücken F5. Ihre Adressliste in Spalte A wird nun alphabetisch nach Straßennamen sortiert.
Diese Version extrahiert nur die Zahl vor dem ersten Leerzeichen und sortiert nach numerischer Reihenfolge.
Fehlerbehebung:
- Bestätigen Sie, dass sich die Adressen in Spalte A befinden oder passen Sie den Code für den Standort Ihrer Daten an.
- Wenn Ihre Daten eine Kopfzeile enthalten, müssen Sie möglicherweise Header = xlYes anpassen, um zu verhindern, dass die Kopfzeile sortiert wird.
- Erstellen Sie immer ein Backup, bevor Sie massenhaft VBA-Code ausführen.
Vorteile: Keine Hilfsspalten erforderlich; funktioniert für große Datensätze oder wiederholtes Sortieren.
Nachteile: Erstkonfiguration erfordert Makroberechtigungen und grundlegendes Verständnis von VBA.
Andere integrierte Excel-Methoden – Verwenden Sie Power Query, um Adressspalten zu teilen und direkt innerhalb von 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 Komponenten wie Hausnummer und Straßennamen aufzuteilen. Diese Lösung ist ideal, wenn Sie Formeln und Hilfsspalten vermeiden möchten oder wenn Ihre Adressen variierende Formate aufweisen, die grundlegende Formeln nicht effizient handhaben können. Power Query kann auch Ihre Schritte speichern, sodass Sie sie aktualisieren können, wenn Ihre Daten wachsen.
1. Wählen Sie Ihre Adressdaten aus und gehen Sie zur Daten Registerkarte, dann wählen Sie Aus Tabelle/Bereich (Erstellen Sie eine Tabelle, falls Sie dazu aufgefordert werden).
2. Im Power Query-Fenster wählen Sie Ihre Adressspalte aus, klicken Sie dann auf Spalte teilen > Nach Trennzeichen. Wählen Sie Leerzeichen als Trennzeichen, und wählen Sie die erste linke Trennung für die Teilen bei Art.
3. Dadurch wird die Adresse in zwei Spalten aufgeteilt: die Hausnummer und der verbleibende Straßennamen/Adresse. Benennen Sie die neuen Spalten bei Bedarf um.
4. Um zu sortieren, klicken Sie auf den Pfeil im Spaltenkopf der Spalte mit dem Straßennamen oder der Hausnummer und wählen Sie Aufsteigend sortieren oder Absteigend sortieren.
5. Klicken Sie auf Schließen & Laden um die sortierten Ergebnisse zurück in Ihr Arbeitsblatt einzufügen.
Zusätzliche Tipps:
- Wenn Ihr Adressmuster nicht konsistent ist, können Sie die Spalten in Power Query weiter bearbeiten, indem Sie benutzerdefinierte Trennungen oder Transformationen verwenden.
- Power Query-Schritte werden automatisch aufgezeichnet; Sie können die Daten leicht aktualisieren, wenn sich Ihre Quelle ändert.
- Diese Methode verändert Ihre Originaldaten nicht, was die Sicherheit der Originalaufzeichnungen erhöht.
Vorteile: Keine dauerhafte Änderung Ihres Blatts; robust für komplexe Adressmuster; keine Formeln zu verwalten.
Nachteile: Erfordert Excel 2016 oder neuer; Schnittstelle kann für neue Benutzer ungewohnt sein.
Zusammenfassung und Fehlerbehebungsvorschläge:
- Denken Sie daran, die Konsistenz Ihres Adressformats zu überprüfen, bevor Sie Formeln oder VBA anwenden.
- Überprüfen Sie immer die Vorschaueinstellungen der Sortierung, um Korrektheit zu bestätigen, insbesondere nach der Verwendung von Hilfsspalten oder Code.
- Bei Daten mit unerwarteter Struktur (wie fehlende Zahlen oder Straßennamen am Ende) passen Sie Formeln an oder erwägen Sie die Verwendung von Power Query für robustere Aufteilungen.
- Erstellen Sie regelmäßige Backups, bevor Sie VBA oder fortgeschrittene Datenwerkzeuge verwenden, um versehentlichen Datenverlust zu vermeiden.
- Wählen Sie eine Lösung (Formeln, VBA, Power Query), die am besten zu Ihrem Datenvolumen, Ihrer Excel-Version und Ihrem Komfortlevel mit dem Tool passt.
- Wenn Sie sich nicht sicher sind, welche Methode am besten ist, bietet Power Query oft die größte Flexibilität und ist am sichersten für nicht-destruktives Bearbeiten.
Verwandte Artikel:
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins