Texte situé avant ou après un groupe de chiffres

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


Sujets que vous pourriez aussi aimer :


Avant et après les chiffres

Avec les fonctions Excel Texte.Avant et Texte.Apres, il est déroutant de simplicité d'extraire depuis des chaînes de textes, les informations placées avant et après des séquences de chiffres. Pour le démontrer, nous suggérons de baser l'étude une fois de plus, sur des adresses postales.



Sur l'exemple illustré par la capture, dans deux colonnes placées sur la droite d'une liste de données, nous parvenons respectivement à extraire l'information placée avant et après un groupe de chiffres. En d'autres termes dans ce cas particulier, nous isolons l'adresse avant le code postal puis la ville après ce même code postal. Et pour cela, nous allons le constater avec plaisir, nous allons bâtir des formules aux syntaxes très épurées.

Classeur Excel à télécharger
Pour résoudre ce cas, nous suggérons d'appuyer les travaux sur ce classeur Excel abritant des adresses postales.

• Télécharger le classeur textes-avant-apres-chiffres.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 trouvons les adresses postales en colonne B. Les informations placées respectivement avant et après le groupe de 5 chiffres sont à extraire distinctement en colonne E et en colonne F.

Après le groupe de 5 chiffres
Pour extraire le nom de la ville après le code postal, nous devons faire en sorte que la fonction Texte.Apres détecte la position du dernier chiffre dans la chaîne. Pour cela, elle doit réaliser son analyse en partant de la fin de l'adresse. Et nous le savons, c'est son troisième paramètre, celui de l'instance, avec une valeur numérique négative, qui permet de raisonner ainsi. De plus, nous allons devoir trouver l'astuce permettant de considérer ou de représenter n'importe lequel des 9 chiffres potentiels à débusquer.

• Sélectionner la case du premier texte après à extraire en cliquant sur sa cellule F4,
• Taper le symbole égal (=) pour débuter la construction de la formule,
• Puis, inscrire la fonction pour prélever après, suivie d'une parenthèse, soit : Texte.Apres(,
• Sélectionner la première adresse en cliquant sur la cellule B4,
• Taper un point-virgule (;) pour passer dans l'argument du délimiteur,

Ce délimiteur peut être tout chiffre de 0 à 9. Mais comme vous l'avez constaté, d'autres chiffres peuvent être présents dans ces adresses, notamment lorsqu'il s'agit de numéros de rue. Pour être certain de prélever l'information après le code postal, l'astuce consiste à se positionner sur le dernier chiffre trouvé dans la chaîne. Et pour détecter tout chiffre, l'idée consiste à construire une matrice de tous ces numéros dans l'argument du délimiteur de la fonction Texte.Apres.

• Construire la matrice de chiffres suivante : {0;1;2;3;4;5;6;7;8;9},
• Taper un point-virgule (;) pour passer dans l'argument du numéro de l'instance,
• Dès lors, taper la valeur -1 pour désigner le dernier chiffre trouvé dans cette première adresse,
• Fermer la parenthèse de la fonction Texte.Apres,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

De cette manière, nous gardons active la cellule du résultat.



Comme vous pouvez le voir, c'est bien la première ville située après le premier code postal qui est isolée. Et naturellement, si vous tirez la poignée de la cellule F4 jusqu'en cellule F11, vous obtenez les extractions de toutes les villes placées après les codes postaux respectifs.

Cependant, cette syntaxe peut être optimisée. Nous l'avons déjà appris, avec la fonction Ligne, nous pouvons construire plus facilement une matrice de chiffres. Le seul souci est que la ligne 0 n'existe pas dans Excel. Or, rien n'interdit qu'un code postal ne commence ou ne se termine par ce chiffre. L'astuce consiste à construire une matrice de numéros variant de 1 et à 10 et de lui retrancher une unité pour représenter tous les chiffres de 0 à 9.

Pour cela, en cellule F4, il suffit d'adapter la précédente syntaxe : =TEXTE.APRES(B4; LIGNE($1:$10)-1; -1) et de la répliquer avec la poignée sur les cellules du dessous.

Avant le groupe de 5 chiffres
Maintenant, pour isoler l'adresse placée devant le code postal, nous devons détecter la position du premier des 5 chiffres composant un code postal. Avec une valeur de 5 unités négatives en troisième paramètre de la fonction Texte.Avant, nous allons être en mesure de nous positionner sur ce dernier, pour prélever ce qui le précède.

• Cliquer sur la cellule E4 pour la sélectionner,
• Taper le symbole égal (=) pour amorcer 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 postale en cliquant sur sa cellule B4,
• Puis, taper un point-virgule (;) pour passer dans l'argument du délimiteur,
• Construire la même matrice de chiffres que précédemment, soit : Ligne($1:$10)-1,

Les dollars sont importants puisque cette formule est amenée à être répliquée sur les lignes du dessous.

• Taper un point-virgule (;) pour passer dans l'argument du numéro de l'instance,

Nous souhaitons prélever ce qui est placé avant le code postal, soit avant le premier chiffre de ce dernier. En partant de la fin de la chaîne (-), ce premier chiffre devient le cinquième.

• Taper la valeur -5,
• Fermer la parenthèse de la fonction Texte.Avant,
• Puis, valider la formule par le raccourci clavier CTRL + Entrée,

Comme vous pouvez l'apprécier, c'est bien le début de la première adresse qui est extrait, jusqu'au code postal.



Bien sûr, si vous tirez la poignée de la cellule E4 jusqu'en cellule E11, vous obtenez toutes les autres extractions avant les codes postaux respectifs.