Isoler la ville, le code postal et la rue d'une adresse

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Découper une adresse postale en VBA

Nous avions déjà appris à fractionner les informations d'une adresse postale avec Excel, grâce à des formules matricielles. Mais les syntaxes abouties étaient relativement complexes. Ici, nous proposons de récidiver en créant des fonctions en VBA. Et nous allons le voir, pour le même résultat, la mise en oeuvre devient tout de suite beaucoup plus simple.

Classeur Excel à télécharger
Nous suggérons d'appuyer l'étude sur un classeur Excel hébergeant des adresses postales à découper.

• Télécharger le classeur fractionner-adresse-postale-vba.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous atteignons la première et unique feuille de ce classeur. Elle offre deux tableaux. Le premier livre les adresses postales complètes de certaines personnes. Le second est encore vide. Il est destiné à recevoir les fonctions VBA Excel permettant de découper ces adresses respectivement sur le lieu (La rue), le code postal et la ville.

La fonction pour le code postal
Nous avons trois fonctions distinctes à créer pour séparer le code postal de la rue et de la ville. Mais nous allons vite le comprendre, c'est une séquence remarquable qui permet de détacher ces informations en toute simplicité. Il s'agit du code postal lui-même. Il est représenté par une séquence immuablement codée sur cinq chiffres. C'est ainsi que nous pourrons extraire le code postal lui-même mais aussi, ce qui se trouve avant et après, soit la rue et la ville.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• En haut de l'éditeur déployer le menu Insertion,
• Puis, choisir l'option Module,

Un nouveau module VBA apparaît dans l'arborescence de l'explorateur de projet sur la gauche de l'écran. Sa feuille de code, encore vierge à ce stade, s'affiche au centre de l'écran.

• Dans cette feuille de code, créer la fonction codePostal comme suit:

Function CodePostal(chaine As String) As String
Dim p As Byte

End Function

Nous déclarons cette fonction avec un paramètre textuel en attente (chaine As String). Il s'agit de l'adresse que l'utilisateur fournira au moment de la construction de la formule. Cette fonction est destinée à retourner une chaîne de texte (As String). En effet, le code postal, bien que codé sur cinq chiffres peut être assimilé à un texte. Ensuite, nous déclarons la variable p (p pour position) comme un entier court (As Byte). Son rôle sera de déterminer la position dans la progression de l'analyse de la chaîne, à la recherche de cette séquence remarquable des cinq chiffres consécutifs.

Parcourir les caractères de l'adresse
Maintenant, pour trouver ce code postal niché au beau milieu de l'adresse, nous devons passer en revue tous les caractères de la chaîne, pour les analyser par groupes de cinq et déterminer s'il s'agit d'une suite de cinq chiffres, donc d'un code postal.

• Après la déclaration, créer la boucle VBA suivante :

...
p = 1
CodePostal = ""
Do While p <= Len(chaine) - 4 And CodePostal = ""

Loop
...

Nous parcourons tous les caractères (Do) tant que (While) la fin de la chaîne n'est pas atteinte. Nous retranchons quatre unité (-4) à cette longueur (Len) pour que l'analyse puisse se faire sur cinq caractères sans dépasser la fin de la chaîne. Notez qu'une adresse se terminant par un code postal reste un cas peu probable. Cependant, nous posons une sécurité. De plus, nous poursuivons cette analyse tant que la variable CodePostal, qui n'est autre que le nom de la fonction, est vide, signifiant que le CP n'a pas encore été décelé. En effet et comme vous le savez, une fonction VBA Excel répond par son propre nom.

Isoler le code postal
Au fil de la progression, nous devons analyser les séquences par groupes de cinq caractères. S'il s'agit d'un enchaînement de cinq chiffres, nous saurons que nous sommes tombés sur le code postal à extraire. La fonction VBA Excel qui permet de découper une chaîne à partir d'une position et sur une longueur donnée, est la fonction Mid.

• Dans les bornes de la boucle, ajouter l'instruction VBA suivante :

...
If Mid(chaine, p, 5) Like "#####" Then CodePostal = Mid(chaine, p, 5) Else p = p + 1
...

Il s'agit d'une instruction conditionnelle. Le test est réalisé sur l'adresse passée en paramètre (chaine) à partir de la position en cours (p) et sur cinq caractères (5). Le symbole du dièse (# : Sharp en anglais) est un caractère générique. Il désigne tout chiffre de 0 à 9. C'est donc en construisant une expression régulière que nous basons la recherche sur le code postal constitué de cinq chiffres. Si cette séquence est rencontrée, nous la stockons dans la variable CodePostal (Le nom de la fonction) grâce à la fonction Mid. Et étant donné le critère de la boucle (And CodePostal = ""), le traitement est stoppé. Dans le cas contraire (else), nous déplaçons l'analyse sur le caractère suivant (p = p + 1), pour étudier la prochaine séquence de cinq caractères.

Extraire le code postal de l'adresse
Notre première fonction est prête. Il ne nous reste plus qu'à savoir si elle est fonctionnelle.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Sélectionner la case du premier code postal à extraire en cliquant sur sa cellule G4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : CodePostal(,
• Désigner la première adresse en cliquant sur sa cellule D4,
• Fermer la parenthèse de la fonction CodePostal,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée pour garder la cellule active,

Comme vous pouvez l'apprécier, le premier code postal (83000) est parfaitement extrait.

• Cliquer et glisser la poignée du résultat à la verticale jusqu'en cellule G11,

De cette manière, nous répliquons la logique d'extraction sur toutes les autres adresses. Et comme vous pouvez le voir, ce sont bien tous les codes postaux qui sont automatiquement extraits des adresses respectives.



Isoler la rue dans l'adresse
Pour extraire la rue et la ville des adresses, les techniques sont identiques. La recherche doit toujours être effectuée sur la séquence remarquable des cinq chiffres consécutifs. Mais cette fois, ce n'est pas la séquence elle-même qui doit être isolée. Il s'agit de la chaîne placée après dans le cas de la ville et de la chaîne placée avant dans le cas de la rue.

• Revenir dans l'éditeur VBA Excel,
• Sous la fonction CodePostal, créer la fonction Rue comme suit :

Function Rue(chaine As String) As String
Dim p As Byte

p = 1
Do While p <= Len(chaine) - 4 And Rue = ""
If Mid(chaine, p, 5) Like "#####" Then Rue = Left(chaine, p - 1) Else p = p + 1
Loop
End Function

Lorsque la séquence (#####) est trouvée, nous prélevons à partir du début de l'adresse (Left), jusqu'au caractère placé avant la séquence (-1) pour ignorer l'espace de séparation.

• Enregistrer les modifications et revenir sur la feuille Excel,
• Activer le premier lieu à trouver en cliquant sur sa cellule F4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Rue(,
• Désigner la première adresse en cliquant sur sa cellule D4,
• Fermer la parenthèse de la fonction Rue,
• Puis, valider la formule par CTRL + Entrée pour garder la cellule active,

Comme vous le constatez, le premier lieu est parfaitement extrait.

• Cliquer et glisser la poignée du résultat jusqu'en cellule F11,

Ce sont bien toutes les rues ou avenues et boulevards qui sont extraits avec leurs numéros.



Isoler la ville dans l'adresse
Pour finir avec la ville, il ne s'agit plus de prélever à partir de la gauche. Il est question de prélever à partir du premier caractère situé après les cinq chiffres et ce, jusqu'à la fin de la chaîne.

• Revenir dans l'éditeur VBA Excel,
• Sous la fonction Rue, créer la fonction Ville comme suit :

Function Ville(chaine As String) As String
Dim p As Byte

p = 1
Do While p <= Len(chaine) - 4 And Ville = ""
If Mid(chaine, p, 5) Like "#####" Then Ville = Mid(chaine, p + 6) Else p = p + 1
Loop
End Function

Grâce à la fonction Mid, nous prélevons jusqu'à la fin, à partir du sixième caractère après la séquence remarquable. En effet, il faut exclure les cinq chiffres ainsi que l'espace qui suit.

• Enregistrer les modifications et revenir sur la feuille Excel,
• Sélectionner la case de la première ville à extraire en cliquant sur la cellule H4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : Ville(,
• Désigner la première adresse en cliquant sur sa cellule D4,
• Fermer la parenthèse de la fonction,
• Puis, valider la formule par CTRL + Entrée pour garder la cellule active,

Une fois encore le résultat est probant. C'est bien la ville de la première adresse qui est extraite.



Et si vous tirez la poignée du résultat jusqu'en cellule H11, ce sont bien toutes les villes des adresses respectives qui sont parfaitement isolées.