Hoe sorteert u adressen op straatnaam/huisnummer in Excel?
Wanneer u een lijst met adressen in Excel beheert, is het vaak noodzakelijk om de gegevens te organiseren of analyseren door adressen te sorteren op straatnaam of straatnummer. Als u bijvoorbeeld klanten die op dezelfde straat wonen wilt groeperen of leveringen in volgorde van huisnummers wilt verwerken, is het essentieel om op deze onderdelen te sorteren. Omdat typische adresformaten echter straatnamen en nummers mengen in één cel, zal eenvoudig sorteren niet de verwachte resultaten opleveren. In dit artikel bespreken we praktische methoden om adressen in Excel te sorteren op straatnaam of straatnummer, analyseren we hun voordelen en toepassingsscenario's, en bieden we oplossingen voor problemen en alternatieve benaderingen voor verschillende gebruikersbehoeften.
Sorteer adressen op straatnaam met een hulpcolumn in Excel
Sorteer adressen op straatnummer met een hulpcolumn in Excel
Sorteer adressen op straatnaam of -nummer met Power Query (geen hulpcolumns)
Sorteer adressen op straatnaam met een hulpcolumn in Excel
Om adressen in Excel op straatnaam te sorteren, moet u eerst alleen de straatnamen extraheren naar een hulpcolumn. Deze aanpak is eenvoudig en werkt goed wanneer het adresformaat consistent is, zoals "123 Appelstraat". Het is geschikt voor snelle projecten of eenvoudige adreslijsten.
1. Selecteer een lege kolom naast uw lijst met adressen. Voer de volgende formule in de eerste cel van de hulpcolumn in om de straatnaam te extraheren:
=MID(A1,FIND(" ",A1)+1,255)(Hier verwijst A1 naar de bovenste cel van uw adresgegevens; pas aan als uw gegevens elders beginnen.)
Na het typen van de formule drukt u op Enter en sleep dan de vulgreep naar beneden om de formule toe te passen op alle rijen in uw adresbereik. Deze formule werkt door de eerste spatie in elk adres te vinden en alles na die spatie terug te geven — de straatnaam en eventuele achtervoegsels. Zorg ervoor dat uw adressen hetzelfde structuur volgen; anders kan de formule mogelijk niet correct splitsen.
2. Markeer de hele hulpcolumn (de kolom met geëxtraheerde straatnamen), ga naar het tabblad Gegevens en klik op Sorteer A-Z. Dit sorteert de straatnamen in oplopende (alfabetische) volgorde.
3. In het dialoogvenster Sorteringswaarschuwing dat verschijnt, selecteert u Selectie uitbreiden om ervoor te zorgen dat de volledige adresinformatie bij elkaar blijft tijdens het sorteren.
4. Klik op Sorteren. Uw adreslijst wordt nu opnieuw gerangschikt op basis van straatnamen, waardoor gelijksoortige straten bij elkaar komen.
Opmerking: Deze methode werkt het beste met gestandaardiseerde adresformaten. Als uw adrescellen onregelmatige patronen bevatten of meerdere spaties vóór de straatnaam hebben, moet de formule mogelijk worden aangepast. Controleer altijd enkele resultaten op nauwkeurigheid na het gebruik van de formule.
Voordelen: Eenvoudig, vereist geen extra tools.
Nadelen: Is afhankelijk van consistente opmaak; extra werk vereist als het adresformaat varieert.
Sorteer adressen op straatnummer met een hulpcolumn in Excel
Als u een lijst met adressen op numeriek straatnummer moet sorteren — bijvoorbeeld voor het toewijzen van een levervolgorde of het identificeren van naburige adressen — is het eenvoudig om het nummer te extraheren en te gebruiken voor sortering. Dit werkt ook effectief wanneer de adressen op verschillende straten liggen.
1. Voer in een lege cel naast uw adreslijst de volgende formule in om het straatnummer te extraheren:
=VALUE(LEFT(A1,FIND(" ",A1)-1))(Waarbij A1 het eerste adres in uw lijst is — pas indien nodig aan.) Druk op Enter nadat u het hebt getypt. Deze formule werkt door de eerste spatie te lokaliseren en de tekens ervoor terug te geven, waarbij ze worden omgezet in een numerieke waarde. Als uw adressen cijfers aan het begin hebben als straatnummers, werkt deze formule correct. Sleep vervolgens de vulgreep naar beneden om de formule toe te passen op de rest van uw lijst.
2. Selecteer de hulpcolumn die u zojuist heeft gemaakt, ga naar het tabblad Gegevens en klik op Sorteer A-Z (of Sorteer Kleinste naar Grootste voor nieuwere Excel-versies).
3. Kies in het dialoogvenster Sorteringswaarschuwing Uitbreiden van de selectie om volledige rijen te sorteren.
4. Klik op Sorteren om toe te passen. Uw adressen zijn nu gesorteerd op het geëxtraheerde straatnummer.
Tip: Als u het straatnummer liever als tekst wilt behouden, of als u geen numerieke sortering hoeft uit te voeren, kunt u ook de volgende formule gebruiken:
=LEFT(A1,FIND(" ",A1)-1)Deze versie extraheert het nummer als een tekststring.
Waarschuwingen: Als adressen beginnen met woorden in plaats van cijfers (zoals "Hoofdstraat5"), werken deze formules niet zoals bedoeld. Controleer uw adresgegevens voordat u de formule gebruikt.
Voordelen: Snel en gebruikersvriendelijk als het adresformaat eenvoudig is.
Nadelen: Kan adressen met namen/achtervoegsels vóór het nummer, noch adressen met meerdere nummers verwerken.
VBA-code - Automatiseer het sorteren van adressen door straatnamen/nummers te extraheren en de lijst met een macro te sorteren
Voor gebruikers die werken met grotere, complexere adreslijsten of waarvan de gegevens variabele adresstructuren omvatten, kan het automatiseren van het sorteerproces met behulp van VBA zeer effectief zijn. VBA stelt u in staat om snel straatnamen of nummers te extraheren, uw adreslijst automatisch te sorteren en handmatige stappen te minimaliseren. Deze oplossing is geschikt wanneer u regelmatig sortering moet uitvoeren of wanneer u sortering in een workflow wilt integreren.
Opmerking: Deze VBA-macro extraheert de straatnaam (het deel na de eerste spatie) uit elk adres in kolom A en sorteert de hele lijst op basis van deze namen. Het werkt ook voor het extraheren en sorteren op straatnummer met kleine aanpassingen.
1. Klik op het tabblad Ontwikkelaar > Visual Basic. Klik in het venster dat verschijnt op Invoegen > Module en plak de volgende VBA-code in het modulevenster:
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. Om de code uit te voeren, klikt u met de actieve adreslijst op de 
knop of druk op F5. Uw adreslijst in kolom A wordt nu alfabetisch gesorteerd op straatnaam.
Deze versie extraheert alleen het nummer vóór de eerste spatie en sorteert op numerieke volgorde.
Problemen oplossen:
- Bevestig dat de adressen zich in kolom A bevinden of pas de code aan voor de locatie van uw gegevens.
- Als uw gegevens een koprij bevatten, moet u mogelijk Header = xlYes aanpassen om te voorkomen dat de koprij wordt gesorteerd.
- Maak altijd een back-up voordat u bulk-VBA-code uitvoert.
Voordelen: Geen hulpcolumns vereist; werkt voor grote datasets of herhaalde sortering.
Nadelen: Initiële instelling vereist macro-rechten en basiskennis van VBA.
Andere ingebouwde Excel-methoden - Gebruik Power Query om adreskolommen te splitsen en direct binnen Power Query te sorteren zonder hulpcolumns
Power Query, beschikbaar in moderne Excel-versies (Excel 2016 en later, evenals Microsoft 365), biedt een flexibele, formulevrije manier om adressen in componenten zoals straatnummer en straatnaam te splitsen. Deze oplossing is ideaal als u formules en hulpcolumns wilt vermijden, of als uw adressen variabele formaten volgen die eenvoudige formules niet efficiënt kunnen verwerken. Power Query kan ook uw stappen opslaan zodat u ze kunt bijwerken terwijl uw gegevens groeien.
1. Selecteer uw adresgegevens en ga naar de Gegevens tab, kies dan Van Tabel/Bereik (maak een tabel aan als u daarom wordt gevraagd).
2. Selecteer in het Power Query-venster uw adreskolom, klik dan op Kolom splitsen > Op scheidingsteken. Kies Spatie als scheidingsteken, en selecteer de eerste linksste scheidingsteken voor de Split op type.
3. Dit splitst het adres in twee kolommen: het straatnummer en de overige straatnaam/adres. Hernoem de nieuwe kolommen indien nodig.
4. Om te sorteren, klikt u op de pijl in de kolomkop van de kolom met straatnamen of straatnummers en selecteert u Sorteer Oplopend of Sorteer Aflopend.
5. Klik Sluiten & Laden om de gesorteerde resultaten terug in uw werkblad in te voegen.
Extra tips:
- Als uw adrespatroon niet consistent is, kunt u kolommen verder manipuleren in Power Query met behulp van aangepaste splitsingen of transformaties.
- Power Query-stappen worden automatisch vastgelegd; u kunt de gegevens gemakkelijk vernieuwen als uw bron verandert.
- Deze methode wijzigt uw originele gegevens niet, wat de veiligheid van originele records verhoogt.
Voordelen: Geen permanente wijziging van uw blad; robuust voor complexe adrespatronen; geen formules om te beheren.
Nadelen: Vereist Excel 2016 of nieuwer; interface kan onbekend zijn voor nieuwe gebruikers.
Samenvatting en suggesties voor probleemoplossing:
- Vergeet niet de consistentie van uw adresformaat te controleren voordat u formules of VBA toepast.
- Controleer altijd de sorteersresultaten om de juistheid te bevestigen, vooral na het gebruik van hulpcolumns of code.
- Voor gegevens met een onverwachte structuur (zoals ontbrekende nummers of straatnamen aan het einde), past u formules aan of overweegt u Power Query voor een robuustere splitsing.
- Maak regelmatig back-ups voordat u VBA of geavanceerde gegevensgereedschappen gebruikt om onbedoeld gegevensverlies te voorkomen.
- Kies een oplossing (formules, VBA, Power Query) die het best past bij uw gegevensvolume, Excel-versie en uw comfortniveau met het gereedschap.
- Als u niet zeker bent welke methode het beste is, biedt Power Query vaak de meeste flexibiliteit en is het veiligst voor niet-destructieve bewerkingen.
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