Comment trier des adresses par nom ou par numéro de rue dans Excel ?
Lorsque vous gérez une liste d’adresses dans Excel, il est souvent nécessaire d’organiser ou d’analyser vos données en triant les adresses soit par nom de rue, soit par numéro de rue. Par exemple, pour regrouper les clients résidant dans la même rue ou planifier des livraisons selon l’ordre croissant des numéros de maison, un tri précis sur ces éléments devient indispensable. Toutefois, comme les formats d’adresse classiques combinent généralement nom et numéro de rue au sein d’une même cellule, un tri direct ne donne pas les résultats escomptés. Dans cet article, nous vous présentons des méthodes pratiques pour trier efficacement vos adresses par nom ou par numéro de rue dans Excel, analysons leurs avantages et leurs cas d’usage, et proposons des solutions de dépannage ainsi que des alternatives adaptées à divers besoins utilisateurs.
Trier les adresses par nom de rue à l’aide d’une colonne auxiliaire dans Excel
Trier les adresses par numéro de rue à l’aide d’une colonne auxiliaire dans Excel
Trier les adresses à l’aide de VBA pour extraire et trier automatiquement par nom ou numéro de rue
Trier les adresses par nom ou numéro de rue avec Power Query (sans colonnes auxiliaires)
Trier les adresses par nom de rue à l’aide d’une colonne auxiliaire dans Excel
Pour trier des adresses par nom de rue dans Excel, commencez par extraire uniquement les noms de rue dans une colonne auxiliaire. Cette méthode, simple et efficace, fonctionne particulièrement bien lorsque le format des adresses est cohérent — par exemple « 123 Apple St ». Elle est idéale pour les projets rapides ou les listes d’adresses simples.
1. Sélectionnez une colonne vide adjacente à votre liste d’adresses, puis saisissez la formule suivante dans la première cellule de cette colonne auxiliaire afin d’extraire le nom de la rue :
=MID(A1,FIND(" ",A1)+1,255)(Ici, A1 fait référence à la cellule située juste au-dessus de vos données d’adresse – adaptez-la si vos données commencent ailleurs.)
Après avoir saisi la formule, appuyez sur Entrée, puis faites glisser la poignée de recopie vers le bas pour appliquer la formule à toutes les lignes de votre plage d’adresses. Cette formule repère le premier espace de chaque adresse, puis renvoie tout ce qui suit cet espace : le nom de rue et tout suffixe éventuel. Vérifiez que vos adresses suivent bien cette structure ; sinon, la formule risque de ne pas effectuer la séparation comme prévu.
2. Mettez en surbrillance toute la colonne auxiliaire (celle contenant les noms de rue extraits), puis accédez à l’onglet Données et cliquez sur Ordre croissant. Cela triera les noms de rue par ordre alphabétique croissant.
3. Dans la boîte de dialogue Avertissement de tri qui s’affiche, sélectionnez Étendre la sélection afin de garantir que toutes les informations d’adresse restent groupées lors du tri.
4. Cliquez sur Trier. Vos listes d’adresses seront désormais réorganisées selon les noms de rue, regroupant ainsi les rues identiques.
Remarque : Cette méthode donne de meilleurs résultats avec des adresses au format standardisé. Si vos cellules contiennent des adresses irrégulières ou comportent plusieurs espaces avant le nom de rue, il se peut que vous deviez ajuster la formule. Vérifiez toujours quelques résultats après utilisation pour confirmer leur exactitude.
Avantages : Simple, ne nécessite aucun outil supplémentaire.
Inconvénients : Dépend d’un formatage cohérent ; du travail supplémentaire est requis si le format de l’adresse varie.
Trier les adresses par numéro de rue à l’aide d’une colonne auxiliaire dans Excel
Si vous devez trier une liste d’adresses par numéro de rue – par exemple pour définir un ordre de livraison ou identifier des adresses voisines – il est facile d’extraire les nombres et de les utiliser pour le tri. Cette méthode reste efficace, même lorsque les adresses se trouvent sur des rues différentes.
1. Dans une cellule vide située à côté de votre liste d’adresses, saisissez la formule suivante pour extraire le numéro de rue :
=VALUE(LEFT(A1,FIND(" ",A1)-1))(où A1 correspond à la première adresse de votre liste – adaptez-la si nécessaire). Appuyez sur Entrée après l’avoir saisie. Cette formule fonctionne en repérant le premier espace et en renvoyant les caractères qui le précèdent, puis en les convertissant en valeur numérique. Si vos adresses commencent par des chiffres correspondant au numéro de rue, cette formule donnera des résultats exacts. Ensuite, faites glisser la poignée de recopie vers le bas pour l’appliquer à toute votre liste.
2. Sélectionnez la colonne auxiliaire que vous venez de créer, accédez à l’onglet Données et cliquez sur Ordre croissant(ou sur)Trier du plus petit au plus grand pour les versions plus récentes d’Excel).
3. Dans la boîte de dialogue Avertissement de tri, choisissez Étendre la sélection pour trier les lignes complètes.
4. Cliquez sur Trier pour appliquer le tri. Vos adresses sont désormais classées selon le numéro de rue extrait.
Astuce :Si vous préférez conserver le numéro de rue sous forme de texte, ou si vous n’avez pas besoin d’un tri numérique, vous pouvez également utiliser :
=LEFT(A1,FIND(" ",A1)-1)Cette version extrait les nombres sous forme de chaînes de texte.
Précautions : Si les adresses commencent par des mots plutôt que par des chiffres (comme « Main Street5 »), ces formules ne fonctionneront pas comme prévu. Vérifiez attentivement vos données d’adresse avant d’utiliser la formule.
Avantages : Rapide et convivial lorsque le format d’adresse est simple.
Inconvénients : Ne prend pas en charge les adresses dont le nom ou le suffixe précède le numéro, ni celles comportant plusieurs chiffres.
Code VBA – Automatiser le tri des adresses en extrayant les noms ou numéros de rue et en triant la liste à l’aide d’une macro
Pour les utilisateurs qui gèrent des listes d’adresses volumineuses ou complexes, ou dont les données comportent des structures d’adresse variables, l’automatisation du tri à l’aide de VBA s’avère particulièrement efficace. Grâce à VBA, vous pouvez extraire rapidement les noms ou numéros de rue, trier automatiquement vos listes d’adresses et éliminer les étapes manuelles fastidieuses. Cette solution est idéale si vous effectuez régulièrement ce type de tri ou si vous souhaitez intégrer cette opération à un flux de travail automatisé.
Remarque : Cette macro VBA extrait le nom de rue (la partie située après le premier espace) de chaque adresse de la colonne A et trie l’ensemble de la liste selon ces noms. Elle peut également être facilement adaptée pour extraire et trier par numéro de rue.
1. Cliquez sur l’onglet Développeur > Visual Basic. Dans la fenêtre qui s’ouvre, cliquez sur Insertion > Module, puis collez le code VBA suivant dans la fenêtre du module :
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. Pour exécuter le code, avec la Liste d'adresses active, cliquez sur le bouton 
ou appuyez sur F5. Vos adresses de la colonne A seront désormais triées par ordre alphabétique selon le nom de rue.
Cette version extrait uniquement le numéro placé avant le premier espace et effectue un tri en ordre numérique.
Dépannage :
– Vérifiez que les adresses se trouvent bien dans la colonne A, ou adaptez le code en fonction de l’emplacement de vos données.
– Si vos données incluent un en-tête, vous devrez peut-être modifier Header = xlYes afin d’éviter de trier la ligne d’en-tête.
– Créez toujours une sauvegarde avant d’exécuter du code VBA en masse.
Avantages : Aucune colonne auxiliaire requise ; idéal pour les grands jeux de données ou les tris répétitifs.
Inconvénients : La configuration initiale exige des autorisations de macro et une connaissance de base de VBA.
Autres méthodes intégrées d’Excel – Utiliser Power Query pour fractionner les colonnes d’adresse et trier directement dans Power Query sans colonnes auxiliaires
Power Query, disponible dans les versions récentes d’Excel (Excel 2016 et ultérieures, ainsi que Microsoft 365), offre une méthode flexible, sans formule, pour fractionner les adresses en composants tels que le numéro et le nom de rue. Cette solution est idéale si vous préférez éviter les formules et les colonnes auxiliaires, ou si vos adresses présentent des formats variés que les formules de base ne peuvent pas gérer efficacement. Power Query permet également d’enregistrer vos étapes afin de les mettre à jour à mesure que vos données évoluent.
1. Sélectionnez vos données d’adresse, puis accédez à l’onglet Données et choisissez À partir d’un tableau/plage (créez un tableau si vous y êtes invité).
2. Dans la fenêtre Power Query, sélectionnez votre colonne d’adresses, puis cliquez sur Fractionner la colonne > Par délimiteur. Choisissez Espace comme délimiteur , et sélectionnez le premier délimiteur le plus à gauche pour le type Fractionner à.
3. Cela divisera l’adresse en deux colonnes : le numéro de rue et le reste du nom de la rue ou de l’adresse. Renommez les nouvelles colonnes si nécessaire.
4. Pour trier, cliquez sur la flèche dans l’en-tête de la colonne contenant les numéros ou les noms de rue, puis sélectionnez Trier par ordre croissant ou Trier par ordre décroissant.
5. Cliquez sur Fermer et charger pour insérer les résultats triés dans votre feuille de calcul.
Conseils supplémentaires :
- Si votre modèle d’adresse manque de cohérence, vous pouvez affiner davantage vos colonnes dans Power Query grâce à des séparations ou des transformations personnalisées.
- Les étapes de Power Query sont enregistrées automatiquement, vous permettant d’actualiser facilement vos données dès que la source est modifiée.
- Cette méthode ne modifie pas vos données d’origine, garantissant ainsi la sécurité de vos enregistrements initiaux.
Avantages : Aucune modification permanente de votre feuille ; robuste face aux formats d’adresse complexes ; aucune formule à gérer.
Inconvénients : Nécessite Excel 2016 ou une version ultérieure ; l’interface peut sembler peu familière aux nouveaux utilisateurs.
Résumé et suggestions de dépannage :
- Vérifiez toujours la cohérence du format de vos adresses avant d’appliquer des formules ou du code VBA.
- Prévisualisez systématiquement les résultats du tri pour en confirmer l’exactitude, notamment après avoir utilisé des colonnes auxiliaires ou du code.
- Pour des données dont la structure est inattendue (comme des numéros ou des noms de rue manquants en fin d’adresse), ajustez vos formules ou privilégiez Power Query pour une séparation plus fiable.
- Effectuez régulièrement des sauvegardes avant d’utiliser du code VBA ou des outils avancés afin d’éviter toute perte accidentelle de données.
- Choisissez une solution (formules, VBA, Power Query) adaptée au volume de vos données, à votre version d’Excel et à votre niveau de maîtrise de l’outil.
- Si vous hésitez sur la méthode à adopter, Power Query offre généralement la plus grande flexibilité et constitue l’option la plus sûre pour une édition non destructive.
Articles associés :
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels