Comment extraire le code postal d'une liste d'adresses dans Excel ?
Lors de la gestion des informations clients dans Excel, il est courant d'avoir une liste d'adresses complètes où chaque entrée contient à la fois les détails de la rue et le code postal. Si vous avez besoin d'isoler et d'extraire uniquement les codes postaux de ces adresses — peut-être pour des listes de diffusion, des analyses régionales ou la mise en forme des données — séparer manuellement chacun d'eux un par un peut être fastidieux et sujet aux erreurs, surtout avec de grands ensembles de données. Heureusement, Excel offre des solutions pratiques pour extraire rapidement et efficacement plusieurs codes postaux à la fois sans effort manuel. Cet article fournit des instructions étape par étape pour extraire les codes postaux des adresses en utilisant une formule Excel et une solution macro VBA.
Extraire le code postal avec une formule dans Excel
Extraire le code postal avec une fonction définie par l'utilisateur dans Excel
Extraire le code postal avec une formule dans Excel
Dans de nombreuses listes d'adresses courantes, le code postal se trouve à la fin de la chaîne d'adresse et ne dépasse pas 8 caractères. Cette méthode convient particulièrement lorsque toutes les adresses de votre liste suivent un format cohérent et que le code postal apparaît comme le dernier élément de chaque adresse, séparé par des espaces.
Pour extraire rapidement le code postal à l'aide d'une formule, suivez ces étapes :
1. Sélectionnez une cellule vide où vous souhaitez que le code postal apparaisse (par exemple, B1 si votre adresse est dans A1). Entrez la formule suivante :
=MID(A1,FIND("zzz",SUBSTITUTE(A1," ","zzz",SUMPRODUCT(1*((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=" "))-1))+1,LEN(A1))2. Appuyez sur la touche Entrée. Le code postal de l'adresse dans A1 sera affiché dans la cellule sélectionnée.
3. Pour appliquer cette formule à d'autres adresses, sélectionnez la cellule contenant la formule, faites glisser la poignée de recopie vers le bas le long de la colonne pour couvrir toutes les lignes d'adresses, et Excel extraira automatiquement le code postal pour chaque adresse.
Astuce : Cette formule suppose que le code postal est le dernier élément après l'espace final dans la chaîne d'adresse. Si vos adresses sont structurées différemment, par exemple des codes postaux apparaissant au milieu ou séparés par des virgules, cette formule risque de ne pas renvoyer le bon résultat. Pour adapter cette méthode aux codes postaux internationaux ou formats personnalisés, vous devrez peut-être utiliser une formule différente ou plus avancée, ou essayer la solution VBA ci-dessous.
Extraire le code postal avec une fonction définie par l'utilisateur dans Excel
Si vos données d'adresse sont plus complexes, incohérentes ou contiennent des codes postaux dans divers formats et positions dans la chaîne comme le montre la capture d'écran suivante, une macro VBA peut offrir des options d'extraction plus flexibles. Cette méthode automatisée traite rapidement toute une colonne d'adresses pour isoler et renvoyer uniquement les codes postaux, économisant ainsi un temps considérable et minimisant les efforts manuels.
1. Appuyez sur Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic for Applications.
2. Dans la fenêtre VBA, cliquez sur Insertion > Module pour créer un nouveau module. Copiez et collez le code VBA suivant dans la fenêtre du module :
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 Function3. Après avoir collé le code, dans la fenêtre de l'éditeur VBA, choisissez Outils > Références. Voir la capture d'écran :
4. Dans la boîte de dialogue Références, cochez Microsoft VBScript Regular Expressions 5.5 et cliquez sur OK.
5. Revenez à la feuille de calcul, et entrez cette formule : =ExtractPostcode(A2), faites glisser la poignée de recopie vers le bas jusqu'à d'autres cellules. Tous les codes postaux sont affichés immédiatement, voir la capture d'écran :
Astuce : Avec ce code, vous pouvez extraire automatiquement les codes postaux de n'importe quel pays ou région dans Excel en quelques secondes ; en ajustant simplement l'expression régulière pour correspondre aux règles des codes postaux de votre zone cible, vous pouvez rapidement vous adapter à différents formats — tels que « SW1A 1AA » au Royaume-Uni, « 12345-6789 » aux États-Unis ou « 100000 » en Chine — augmentant ainsi considérablement l'efficacité de votre nettoyage et analyse de données.
Articles connexes :
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels