Numéro et nom de rue avant le code postal avec Excel

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


Sujets que vous pourriez aussi aimer :


L'adresse avant le code postal

Nous poursuivons la mise en pratique de la fonction Texte.Avant. Nous allons le voir, en embarquant une fonction de recherche dans son argument du délimiteur, elle est capable de déceler la présence d'une séquence remarquable, comme celle d'un code postal.



C'est ainsi, comme son nom l'indique et comme l'illustre la capture, à partir d'adresses postales complètes, que nous sommes capables d'isoler tout ce qui se trouve avant le CP, soit généralement le numéro et le nom de la rue.

Classeur Excel à télécharger
Pour développer cette solution, nous suggérons d'appuyer l'étude sur un classeur Excel offrant des adresses postales à analyser.

• Télécharger le classeur adresse-avant-cp.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écouvrons la liste des adresses postales en colonne B. Une rangée d'extraction est présente en colonne E. Elle est en attente de la formule permettant d'extraire les informations situées devant chaque code postal.

Déclencher le fractionnement
Avant de songer à déceler la présence de la séquence remarquable des cinq chiffres composant un code postal, nous devons enclencher la fonction de fractionnement sur la première adresse, en cellule B4.

• Cliquer sur la première case de la colonne d'extraction pour sélectionner la cellule E4,
• Taper le symbole égal (=) pour démarrer la construction de la formule,
• Inscrire la fonction pour prélever avant, suivie d'une parenthèse, soit : Texte.Avant(,
• Désigner la première adresse en cliquant sur la cellule B4,
• Puis, taper un point-virgule (;) pour passer dans l'argument du délimiteur,

Découper la chaîne
C'est lui que nous devons déceler dans la chaîne. Et pour cela, nous devons la fractionner par fragments de cinq caractères, dans un traitement récursif consistant à tous les passer en revue.

• Inscrire la fonction de découpe, suivie d'une parenthèse, soit : Stxt(,
• Désigner de nouveau la première adresse en cliquant sur sa cellule B4,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la position de l'analyse,

Chercher le code postal
C'est cette analyse que nous devons enclencher sur le début de l'adresse et faire progresser sur ses caractères tant que la séquence des cinq chiffres n'est pas trouvée. Et pour cela, comme nous l'avons déjà fait par le passé, nous allons activer la recherche avec la fonction Equiv, sur un test logique.

• Inscrire la fonction de recherche, suivie d'une parenthèse, soit : Equiv(,
• Taper le booléen Vrai,

Tester la séquence des 5 caractères
C'est ce booléen que nous devons recouper avec un test logique sur les séquences successives des cinq caractères à analyser. C'est la fonction EstNum qui permet de savoir si une chaîne est un nombre. Quand elle renverra vrai, elle indiquera que cette séquence a été trouvée. Nous pourrons alors exploiter la position décelée pour prélever ce qui est placé avant le code postal.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

En guise de tableau, il s'agit bien sûr des séquences successives de cinq caractères.

• Inscrire la fonction de test numérique, suivie d'une parenthèse, soit : EstNum(,

Dans ce traitement récursif, pour analyser ces séquences les unes après les autres, nous devons engager des découpes successives. Donc, la fonction Stxt est de nouveau de mise.

• Inscrire la fonction de découpe, suivie d'une parenthèse, soit : Stxt(,
• Désigner une fois encore la première adresse en cliquant sur sa cellule B4,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la position de départ,

C'est elle que nous devons faire varier du premier au dernier caractère, pour les analyser tous par groupes de 5. Pour cela et grâce à la fonction Ligne, nous allons construire une matrice de nombres suffisamment grande pour couvrir les adresses les plus longues.

• Construire la matrice suivante : LIGNE($1:$100),

Ainsi, nous réalisons une analyse récursive des groupes sur au moins cent caractères. Les dollars sont importants car nous allons répliquer la formule sur les lignes du dessous. En parlant de groupe, ces séquences numériques à déceler sont faites de cinq chiffres.

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

De fait, nous sommes de retour dans les bornes de la fonction EstNum. Comme ces groupes de cinq caractères sont issus d'une chaîne de texte, nous devons d'abord tenter leurs conversions en nombres avant de les passer en test à la fonction EstNum. Pour cela, il suffit de multiplier le groupe en cours d'analyse par le chiffre 1.

• Forcer la conversion, soit : *1,
• Fermer la parenthèse de la fonction EstNum,

De fait, nous sommes de retour dans les bornes de la fonction Equiv qui, lorsque le test sera avéré, connaîtra la position du code postal dans la chaîne.

• Taper un point-virgule suivi du chiffre zéro, soit : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,

C'est ainsi que nous sommes de retour dans les bornes de la première fonction Stxt.

• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Taper le chiffre 5 pour prélever sur cinq caractères,

La fonction Equiv a trouvé la position du code postal qu'elle a transmise à cette fonction Stxt. En prélevant sur cinq caractères, nous extrayons le code postal que nous transmettons à la fonction Texte.Avant qui englobe tout le calcul. Elle va donc l'utiliser comme délimiteur pour isoler tout ce qui est placé avant, soit le début de l'adresse jusqu'au code postal.

• Fermer la parenthèse de la fonction Stxt,
• Fermer la parenthèse de la fonction Texte.Avant,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

De cette façon, nous conservons active la cellule du résultat.



Comme vous pouvez l'apprécier, la première extraction est un succès. Le prélèvement s'arrête juste avant le code postal. Et si vous cliquez et glissez la poignée de la cellule E4 jusqu'en cellule E11, vous avez le plaisir de constater que ce sont toutes les découpes des adresses postales qui sont parfaitement exécutées.