Séparer les nombres des textes 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 :


Séparer les nombres des textes

C'est une nouvelle astuce Excel épatante qui se propose dans ce nouveau chapitre. Son rôle est d'extraire n'importe quel chiffre ou n'importe quel nombre emprisonné au beau milieu d'une chaîne de texte. Il s'agit d'une solution qui justifie elle-même sa grande utilité. Une technique courante sur le Web consiste à renommer une image ou photo uploadée avec les informations de date et heure, pour garantir l'unicité de l'intitulé alloué. Dès lors, si nous sommes capables d'isoler les nombres, nous sommes capables d'extraire les mots clés décrivant la photo.



Sur l'exemple illustré par la capture, une chaîne compacte est inscrite dans une cellule fusionnée, au-dessus des deux petits tableaux. Elle mélange des mots clés et des informations numériques sur la date, dont la précision descend jusqu'à la minute. Dans le premier tableau sur la gauche, nous décelons la position de chacun de ces nombres. Et grâce à ces calculs, dans le second tableau sur la droite, nous isolons ces informations numériques. Et pour résoudre le cas, ce sont des techniques matricielles très simples qui entrent en vigueur.

Classeur source à télécharger
Pour la démonstration de cette nouvelle astuce Excel, nous proposons d'élaborer la solution à partir d'un classeur offrant cette structure préconçue mais aussi une précieuse matrice de chiffres rangée dans une plage nommée.

• Télécharger le classeur separer-nombres-et-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 l'assemblage de textes et de nombres en cellule fusionnée B3. Les positions de ces différents indicateurs de date sont à trouver en colonne C. Grâce à ces résultats, les données numériques sont à extraire de la chaîne en colonne F.

• En haut de la fenêtre Excel, cliquer sur l'onglet Formules pour activer son ruban,
• Dans la section Noms définis du ruban, cliquer sur le bouton Gestionnaire de noms,

Comme vous pouvez le voir, un nom a effectivement été défini. Il s'agit d'une matrice représentant tous les chiffres et qui porte l'intitulé chiffres : ={0;1;2;3;4;5;6;7;8;9}. Grâce à elle et à un raisonnement matriciel capable de passer en revue tous les éléments des matrices confrontées dans une seule formule, nous allons pouvoir déceler les positions des nombres avec des calculs simples.

Position de l'année
L'année est la première information numérique dans cette chaîne alphanumérique. Dans un raisonnement matriciel, nous devons rechercher (fonction Cherche) la première position (fonction Min) de l'un des chiffres de cette matrice nommée chiffres.

• Cliquer sur le bouton Fermer du gestionnaire de noms pour revenir sur la feuille Excel,
• Sélectionner la case de la position de l'année à trouver en cliquant sur sa cellule C5,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction donnant le plus petit chiffre, suivie d'une parenthèse, soit : Min(,
• Inscrire la fonction de gestion des anomalies suivie d'une parenthèse, soit : SiErreur(,

En effet, pour trouver la première position (plus petite), nous allons maintenant engager une recherche des chiffres de la matrice dans la chaîne de texte grâce à la fonction Cherche. Or, une fonction de recherche lorsqu'elle ne trouve pas, répond par une erreur. Et c'est ce qui va se produire sur les deux premiers caractères car il s'agit de lettres. Pour ne pas saborder le calcul, cette fonction permet d'ignorer les erreurs pour poursuivre l'analyse sur les caractères suivants.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Cherche(,
• Saisir le nom de la matrice des chiffres, soit : chiffres, en guise d'éléments cherchés,
• Puis, taper un point-virgule (;) pour passer dans l'argument du texte dans lequel rechercher,
• Désigner la chaîne alphanumérique en cliquant sur sa cellule fusionnée B3,
• Fermer la parenthèse de la fonction Cherche,
• Taper un point-virgule (;) pour passer dans le second argument de la fonction SiErreur,
• Inscrire deux guillemets ("") pour ignorer les erreurs sur les lettres,
• Fermer la parenthèse de la fonction SiErreur,
• Fermer la parenthèse de la fonction Min,
• Puis, valider la formule avec le raccourci clavier CTRL + MAJ + Entrée,

Ce raccourci permet de transformer la formule en calcul matriciel. C'est ainsi que chaque chiffre de la matrice est cherché tour à tour dans la chaîne de texte. La fonction Cherche trouve ces positions. La fonction Min renvoie la plus petite de la matrice résultante. Il en découle la position du premier chiffre, soit de l'année. Vous pouvez consulter cette matrice résultante, en plaçant le point d'insertion sur le nom de la fonction SiErreur dans la formule et en cliquant sur le bouton de l'assistant fonction (fx).



Comme vous pouvez le voir, c'est bien le chiffre 3 le plus petit, correspondant effectivement à la position de l'année dans le texte.

Remarque : Il est à noter que ce raccourci clavier CTRL + MAJ + Entrée n'est plus nécessaire à partir de la version 2019 d'Excel et d'Office 365. En raison de la matrice utilisée en argument de la fonction Cherche, Excel comprend instinctivement qu'il doit engager un traitement récursif, soit un raisonnement matriciel.

La syntaxe du calcul que nous avons construit pour trouver la position de l'année est la suivante :

=MIN(SIERREUR(CHERCHE(chiffres; B3); ""))

Positions des nombres suivants
Maintenant, pour trouver les positions des données numériques suivantes comme le mois et le jour, la technique est similaire à un détail près. Il faut relancer la recherche après la dernière position trouvée. Ainsi, la nouvelle position considérée la plus petite sera bien la suivante. Et pour cela, nous devons simplement renseigner le troisième paramètre facultatif de la fonction Cherche. Il concerne la position de départ pour la recherche.

• Pour la position du mois en C6, adapter la précédente syntaxe comme suit :

=MIN(SIERREUR(CHERCHE(chiffres; B3; C5+4); ""))

• Puis, la valider avec le raccourci clavier CTRL + MAJ + Entrée,

Nous débutons la recherche à partir de la position trouvée pour l'année, incrémentée de quatre unités, pour sauter les quatre chiffres qu'elle comporte.

Le résultat confirme que le mois se trouve en quatorzième position des caractères dans cette chaîne.

Pour les positions suivantes, nous allons pouvoir répliquer une même formule, étant donné que chaque donnée numérique à déceler est constituée du même nombre de chiffres, 2 en l'occurrence.

• En cellule C7, adapter la précédente syntaxe comme suit :

=MIN(SIERREUR(CHERCHE(chiffres; $B$3; C6+2); ""))

Attention ! Etant donné que nous allons répliquer cette formule, la cellule dans laquelle nous recherchons ces informations numériques doit être figée, d'où la présence des dollars encadrant les coordonnées de la cellule B3.

• Valider le calcul par le raccourci clavier CTRL + MAJ + Entrée,
• Puis, cliquer et glisser la poignée du résultat en dessous jusqu'en cellule C10,

Comme vous pouvez le voir, toutes les positions tombent les unes à la suite des autres.

Extraire les nombres des textes
Grâce à ces positions calculées, l'extraction des informations de date à partir de la chaîne de texte est désormais un jeu d'enfant. L'année est la seule donnée constituée de quatre chiffres. Toutes les autres sont composées de deux chiffres. Et c'est la fonction Excel Stxt qui permet d'isoler un fragment à partir d'un texte, en fonction d'une position de départ et d'une longueur de découpe.

• Sélectionner la case de l'année à extraire en cliquant sur sa cellule F5,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner le texte à partir duquel prélever en cliquant sur sa cellule fusionnée B3,
• Taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Désigner la position trouvée pour l'année en cliquant sur sa cellule C5,
• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Inscrire le chiffre 4 pour prélever les quatre chiffres de l'année,
• Fermer la parenthèse de la fonction Stxt,
• Puis, valider la formule à l'aide de la touche Entrée du clavier.

Comme vous pouvez l'apprécier, ce sont strictement les quatre chiffres de l'année qui sont parfaitement extraits de cette chaîne de texte pourtant relativement complexe.

Pour finir avec la formule réplicable pour les autres informations de date et heure :

• En cellule F6, construire la syntaxe suivante : =Stxt($B$3;C6;2),

Attention, la cellule B3 doit être figée avec les dollars pour les mêmes raisons que précédemment.

• Valider le calcul avec le raccourci clavier CTRL + Entrée ,
• Puis tirer la poignée du résultat sur les lignes du dessous jusqu'en cellule F10,

Comme vous pouvez le voir, nous avons réussi à isoler indépendamment chaque nombre contenu à l'origine dans la chaîne de caractères.