Découper une adresse postale par formule Excel

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

A l'occasion de la précédente astuce Excel, nous avons appris à séparer le Nom du Prénom dans une chaîne de texte. Et pour cela, dans un raisonnement matriciel analysant toutes les lettres, nous avions entrepris de déceler la position de la première suite de caractères en majuscules. C'est cet indicateur qui permettait d'affirmer que le nom de famille était atteint.

Nous souhaitons maintenant décliner cette technique sur une adresse postale afin de fragmenter les informations pour dessiner une véritable base de données. Il s'agit d'isoler d'une part l'adresse et d'autre part le code postal puis la ville. Et dans ces chaînes, une séquence remarquable existe aussi. Le code postal est effectivement constitué d'une suite immuable de 5 chiffres. En décelant sa position, nous saurons prélever ce qui existe avant (L'adresse) mais aussi après (La Ville).

Classeur source
Pour la mise en place de la solution, sous suggérons d'appuyer l'étude sur un classeur offrant une liste de clients avec des adresses postales à décortiquer.

• Télécharger le classeur decouper-adresse-postale.xlsx 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 débouchons sur l'unique feuille de ce classeur.



Sur la gauche, un premier tableau est composé de cinq colonnes. Les Noms et Prénoms des clients sont identifiés dans les deux premières colonnes. Les informations détachées sur l'adresse, le code postal et la ville sont attendues dans les trois dernières colonnes vides. Et précisément, un second tableau constitué d'une seule colonne et placé sur la droite, énumère toutes ces adresses postales respectives.

Isoler l'adresse
Pour séparer le début de l'adresse du reste, nous devons engager la fonction Excel Stxt dans un raisonnement matriciel. La fonction de découpe doit être en mesure d'analyser tous les caractères par groupes de 5. Dès qu'un groupe répondra favorablement à la fonction EstNum, nous saurons que la suite n'est composée que de chiffres. Grâce à la fonction Equiv, nous en déduirons la position du code postal. Et cette position, nous la passerons à la fonction Excel Gauche pour prélever tous les caractères situés avant, afin d'isoler l'adresse uniquement.

• Sélectionner la première adresse à isoler en cliquant sur la cellule D4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction pour prélever à partir du début, suivie d'une parenthèse, soit : Gauche(,
• Désigner le texte à découper en cliquant sur la première adresse en cellule H4,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,

C'est ici que l'astuce matricielle doit entrer en jeu pour déceler la position du code postal dans la chaîne.

• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,

Pour déceler le point de départ, cette fonction doit vérifier un test. Il s'agit de tomber sur la première suite de cinq caractères constituée exclusivement de chiffres. Donc, dans ce raisonnement matriciel, en guise d'élément à chercher, nous allons lui passer une valeur booléenne faisant office d'indicateur de succès.

• Inscrire le booléen Vrai,
• Puis, taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

En guise de tableau, ce sont tous les caractères de la chaîne que nous devons parcourir par groupes de 5 dans cette analyse matricielle récursive. Et comme nous l'évoquions, il est question de tester si ce groupe est numérique.

• Inscrire la fonction de test numérique suivie d'une parenthèse, soit : EstNum(,
• Inscrire alors la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner de nouveau l'adresse postale à analyser en cliquant sur sa cellule H4,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la position de départ,

Dans cette analyse matricielle, ce sont tous les caractères qui doivent être passés en revue, par groupes de 5. Donc, l'indice de départ pour la découpe doit varier et progresser. Pour cela, nous allons lui passer une matrice virtuelle. Et nous allons prévoir des bornes suffisamment larges pour être en mesure d'analyser les adresses postales longues.

• Dès lors, construire la matrice virtuelle suivante : Ligne($1:$255),

Grâce à la fonction Ligne incluse dans cette analyse récursive, nous allons faire varier l'indice de prélèvement en partant de la position 1 et en progressant jusqu'à l'indice 255 si d'aventure certaines adresses postales particulièrement longues semaient le chemin d'embûches. Notez que les indices doivent absolument être figés ($) pour ne pas progresser en même temps que la formule sera répliquée sur les lignes du dessous.

• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Inscrire le chiffre 5 et fermer la parenthèse de la fonction Stxt,

Effectivement, un code postal est nécessairement composé de cinq chiffres. Grâce à la matrice virtuelle passée dans l'argument de la position de départ, ce sont tous les groupes de 5 caractères qui vont être analysés tour à tour jusqu'à la fin de l'adresse postale. Néanmoins à ce stade, ces 5 caractères sont imbriqués dans une cellule de texte. Donc, qu'ils soient numériques ou textuels, ils vont par défaut être considérés comme du texte. Pour valider le test par la fonction EstNum, nous devons tenter de forcer la conversion en nombre. Pour cela, il suffit de multiplier la chaîne des 5 caractères par le chiffre 1.

• Forcer la conversion en nombre comme suit : *1,
• Dès lors, fermer la parenthèse de la fonction EstNum,

De fait, nous sommes de retour dans les bornes de la fonction Equiv.

• Taper un point-virgule suivi du chiffre zéro : ;0 pour réaliser une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Gauche,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Comme vous pouvez le voir, la première adresse est parfaitement extraite. Le prélèvement s'arrête au code postal.

• Double cliquer sur la poignée du résultat pour répandre la logique sur la hauteur du tableau,

Nous sommes donc parvenus à isoler toutes les adresses en excluant tout ce qui se situe à partir du code postal et ce, malgré des emplacements fort variables. La syntaxe complète de la formule matricielle que nous avons bâtie est la suivante :

{=GAUCHE(H4; EQUIV(VRAI; ESTNUM(STXT(H4; LIGNE($1:$255); 5)*1); 0))}

Isoler le Code Postal de l'adresse
Désormais, nous devons détacher le code postal de l'adresse pour l'isoler en colonne E. La technique est connue, nous venons de la construire. Dans ce raisonnement récursif, c'est la fonction Equiv qui permet de déceler la position de cette séquence remarquable de 5 chiffres. Nous devons de nouveau l'exploiter. Mais cette fois, il n'est pas question de prélever à partir du début de la chaîne. Il est question de prélever à partir du code postal et sur 5 caractères. Nous devons donc imbriquer la syntaxe de la fonction Equiv dans la fonction Stxt.

• Dans la barre de formule de la cellule D4, prélever et copier la syntaxe de la fonction Equiv :

EQUIV(VRAI; ESTNUM(STXT(H4; LIGNE($1:$255); 5)*1); 0)

• Sortir de la barre de formule avec la touche Echap du clavier pour ne pas affecter la syntaxe,
• Sélectionner le premier code postal à isoler en cliquant sur la cellule E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner l'adresse postale à analyser en cliquant sur sa cellule H4,
• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Coller la syntaxe de la fonction Equiv précédemment construite,

Ainsi, nous indiquons de prélever dans la chaîne à partir de la position du code postal.

• Ajouter une unité à ce score, soit : +1,

Il y a en effet un décalage entre l'indice zéro de départ dans la chaîne et la position de début dans la matrice virtuelle. Cette incrémentation permet donc de sauter l'espace situé devant le code postal et sur lequel nous étions de fait arrêtés.

• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Inscrire le chiffre 5 pour prélever les 5 numéros du code postal,
• Fermer la parenthèse de la fonction Stxt,
• Puis, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez l'apprécier, l'extraction du premier code postal est parfaitement réalisée.

• Double cliquer sur la poignée de la cellule pour propager la formule sur la hauteur du tableau,

Tous les codes postaux sont isolés du reste de l'adresse et ce, toujours à l'aide d'une seule formule matricielle. La syntaxe de la formule que nous avons construite par assemblage est la suivante :

{=STXT(H4; EQUIV(VRAI; ESTNUM(STXT(H4; LIGNE($1:$255); 5)*1); 0)+1; 5)}

Isoler la Ville de l'Adresse
Maintenant, pour détacher la ville de l'adresse postale, le principe est bien évidemment toujours le même, toujours sur la base de la recherche exercée par la fonction Equiv. Il s'agit de prélever tout ce qui se trouve après ces 5 chiffres, grâce à la fonction Stxt bien sûr.

=STXT(H4; EQUIV(VRAI; ESTNUM(STXT(H4; LIGNE($1:$255); 5)*1); 0)+6; 200)

Dans la syntaxe précédente pour l'extraction du code postal, il suffit simplement de réaliser deux petits ajustements. Tout d'abord l'incrémentation d'une unité doit être poussée à six unités (+6) pour sauter le code postal. Enfin, en dernier argument de la fonction Stxt, il s'agit d'indiquer une longueur de découpe importante (200) pour considérer les villes aux noms longs. La fonction Stxt s'en accommodera en cas de dépassement et s'ajustera sur la borne de fin de la chaîne.