Isoler les chiffres au milieu des textes Excel

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


Sujets que vous pourriez aussi aimer :


Séparer les nombres des textes

Avec cette nouvelle astuce Excel, nous allons apprendre à isoler les chiffres situés au beau milieu des chaînes de textes. Pour aboutir à un tel résultat, un raisonnement matriciel est nécessaire. Sans l'appui du code VBA, il est effectivement le seul capable de parcourir chaque caractère d'une cellule pour l'étudier.



Dans l'exemple finalisé illustré par la capture, des codes alphanumériques sont inscrits en première colonne du tableau. En deuxième colonne, la position du premier chiffre est décelée. En troisième colonne, le décompte des lettres est effectué. En quatrième colonne, une formule matricielle consolide les deux précédentes étapes pour extraire les nombres des chaînes de texte. C'est donc une seule formule à répliquer qui permet d'atteindre le résultat. Mais pour la bonne compréhension du mécanisme, nous proposons de découper la solution en trois étapes.

Classeur source
Un classeur offrant ce tableau avec des codes alphanumériques existe et nous proposons tout d'abord de le récupérer.

• Télécharger le classeur extraire-chiffres-des-textes.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 retrouvons bien le tableau aux quatre colonnes. La première renferme des codes alphanumériques tandis que les trois suivantes sont naturellement vides à ce stade.

Position du premier chiffre dans le texte
Nous devons commencer par déceler la position de la première donnée numérique dans la chaîne de texte. Pour cela, nous allons exploiter la fonction Equiv. Son objectif est de reconnaître tout ce qui n'est pas un chiffre pour poursuivre la recherche sur les caractères suivants. Et dans ce raisonnement matriciel, nous allons l'employer à contre-courant. Tout ce qui ne peut pas être multiplié génère une erreur. Nous allons donc lui demander, caractère à caractère, de déceler le premier qui justement n'aboutit pas à une anomalie.

• Sélectionner la case de la première position à trouver en cliquant sur la cellule D4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• En guise de valeur cherchée, taper le chiffre 0,

Il fait office de valeur booléenne. Grâce à lui, nous allons pouvoir trouver tout ce qui ne génère pas une erreur. Et pour cela, nous allons engager une matrice de recherche sur les caractères dans la fonction EstErreur. Lorsque cette dernière rencontrera une anomalie, l'équivalence ne sera pas considérée jusqu'à ce qu'elle tombe sur un chiffre.

• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Inscrire la fonction de test d'anomalie suivie d'une parenthèse, soit : EstErreur(,

Désormais, pour analyser chaque caractère tour à tour dans ce raisonnement matriciel, nous devons engager une fonction de découpe. Et c'est la fonction Excel Stxt qui permet de prélever un bout de chaîne sur une longueur à déterminer, un caractère dans notre cas.

• Inscrire la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner le premier code alphanumérique par ses coordonnées, soit : C4,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la position de départ,

Dans ce raisonnement récursif, cette position doit être incrémentée à chaque passage pour que chaque caractère soit analysé indépendamment. Et pour cela, nous allons passer à la fonction Ligne une matrice virtuelle de même longueur que le nombre de caractères contenus dans le code.

• Inscrire la fonction Ligne suivie d'une parenthèse, soit : Ligne(,

La matrice à lui passer doit être construite de toutes pièces. En conséquence, elle doit être interprétée pour faire référence à des positions.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Saisir le chiffre 1 suivi du symbole deux points entre guillemets, soit : "1:",

De cette manière, nous définissons la borne de départ de la matrice, à partir de la première ligne, soit à partir du premier caractère dans ce raisonnement matriciel. Elle doit être assemblée avec la borne de fin qui est variable. Elle dépend du nombre de caractères contenus dans la chaîne analysée.

• Taper un espace suivi du symbole de concaténation (&) suivi d'un espace,
• Inscrire la fonction comptant les caractères, suivie d'une parenthèse, soit : NbCar(,
• Désigner de nouveau le code alphanumérique par ses coordonnées, soit : C4,
• Fermer la parenthèse de la fonction NbCar,
• Fermer la parenthèse de la fonction Indirect,
• Puis, fermer la parenthèse de la fonction Ligne,

De fait, nous sommes de retour dans les bornes de la fonction Stxt. Nous avons indiqué quel était le texte à découper, mais aussi à partir de quelle position variable la découpe devait débuter. Il nous reste à définir le nombre de caractères à prélever.

• Taper un point-virgule suivi du chiffre 1, soit : ;1, pour prélever un caractère tour à tour,
• Dès lors, fermer la parenthèse de la fonction Stxt,

Nous sommes de retour dans les bornes de la fonction EstErreur.

C'est là que nous devons tenter la multiplication. S'il s'agit d'une lettre, la fonction EstErreur réagira et ne répondra pas favorablement à la recherche exercée par la fonction Equiv.

• Multiplier le caractère isolé par 1, soit : *1,
• Fermer alors la parenthèse de la fonction EstErreur,
• Puis, réaliser de nouveau la même multiplication, soit : *1,

Il s'agit d'une double sécurité pour forcer la conversion numérique lorsqu'elle est possible.

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

Le premier résultat tombe et il indique que le premier chiffre intervient en quatrième position, ce qui est tout à fait le cas.

• Double cliquer sur la poignée du résultat pour propager la formule sur la hauteur du tableau,

Toutes les positions des chiffres sont ainsi dévoilées. La première étape est donc aboutie avec succès puisque nous savons dynamiquement à partir de quelle position prélever pour isoler les chiffres des lettres. Mais encore faut-il savoir sur quelle longueur cette ponction doit se faire.

Nombre de lettres dans la chaîne
Pour connaître le nombre de chiffres à prélever dans la chaîne de texte, l'astuce consiste à connaître le nombre de lettres, selon le même mécanisme que précédemment. Nous pouvons sommer les erreurs générées. La différence entre le nombre total des caractères et ce score fournira la quantité de chiffres, soit la longueur de découpe pour les isoler.

La syntaxe complète de la précédente formule matricielle est la suivante :

{=EQUIV(0; ESTERREUR(STXT(C4; LIGNE(INDIRECT("1:" & NBCAR(C4))); 1)*1)*1; 0)}

• Y prélever (CTRL + C) la portion sur le test des erreurs :

ESTERREUR(STXT(C4; LIGNE(INDIRECT("1:" & NBCAR(C4))); 1)*1)

• Cliquer sur la cellule E4 pour la sélectionner,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Inscrire la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Puis, coller (CTRL + V) le précédent calcul copié,
• Forcer de nouveau la multiplication par 1, soit : *1,
• Fermer la parenthèse de la fonction Somme,
• Puis, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Le premier résultat indique avec justesse que le premier code est constitué de cinq lettres.

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

Comme vous pouvez le voir, toutes les lettres sont parfaitement comptées en excluant les chiffres.

Isoler les chiffres
Le plus dur est fait pour séparer les chiffres des lettres. Nous disposons désormais de toutes les informations nécessaires. Et nous allons prendre soin de réunir les précédentes syntaxes dans un unique calcul. Il nous suffit de prélever dans la chaîne à partir de la position du premier chiffre. Cette information est renvoyée par le premier calcul matriciel. La découpe doit être engagée sur le nombre de chiffres qui se suivent. Ce nombre peut être obtenu en réalisant la différence entre le nombre total de caractères et le nombre total de lettres. Cette quantité est livrée par le deuxième calcul matriciel.

• Copier l'intégralité de la syntaxe de la première formule matricielle, soit :

=EQUIV(0; ESTERREUR(STXT(C4; LIGNE(INDIRECT("1:" & NBCAR(C4))); 1)*1)*1; 0)

• Cliquer alors sur la cellule F4 pour la sélectionner,
• 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 le premier code alphanumérique en cliquant sur sa cellule C4,
• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Puis, coller le calcul précédemment copié,
• Prélever et copier la syntaxe de la fonction EstErreur, soit :

ESTERREUR(STXT(C4;   LIGNE(INDIRECT("1:" & NBCAR(C4)));   1)*1)

• Puis, replacer le point d'insertion à la toute fin de la syntaxe en cours,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur à prélever,
• Inscrire la fonction pour compter les caractères, suivie d'une parenthèse, soit : NbCar(,
• Désigner de nouveau le premier code alphanumérique par ses coordonnées, soit : C4,
• Fermer la parenthèse de la fonction NbCar,

Pour obtenir le nombre de chiffres renseignant sur la longueur de prélèvement, nous devons lui soustraire le nombre de lettres.

• Taper le symbole moins (-) pour engager la soustraction,
• Taper la fonction d'addition suivie d'une parenthèse, soit : Somme(,
• Puis, coller (CTRL + V) la syntaxe précédemment copiée,
• Forcer la conversion par la multiplication (*1),
• Fermer la parenthèse de la fonction Somme,
• Fermer la parenthèse de la fonction Stxt,
• Enfin, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Le premier résultat extrait parfaitement le nombre du texte comme vous pouvez l'apprécier.

• Double cliquer sur la poignée de ce résultat pour répliquer la logique sur le tableau,

Nous avons réussi à isoler tous les nombres contenus dans les cellules de texte.

La syntaxe complète de la formule matricielle permettant de séparer les chiffres des lettres est la suivante :

{=STXT(C4; EQUIV(0; ESTERREUR(STXT(C4; LIGNE(INDIRECT("1:" & NBCAR(C4))); 1)*1)*1; 0); NBCAR(C4) - SOMME(ESTERREUR(STXT(C4; LIGNE(INDIRECT("1:" & NBCAR(C4))); 1)*1)*1))}