Liste de codes dans des chaînes de 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 :


Trouver des codes dans des chaînes

Avec une nouvelle technique matricielle, nous allons voir comment trouver des codes spécifiques dans des chaînes de textes pour extraire ces dernières en guise de résultats de recherche.



Des chaînes alphanumériques assez longues sont inscrites dans une première colonne. Sur la droite, des codes de quelques lettres appartenant à certaines chaînes, sont destinés à la recherche. Et c'est alors une formule matricielle qui extrait et regroupe les chaînes concernées. Si l'utilisateur tape un nouveau code à la suite des autres, sa chaîne est automatiquement extraite en-dessous des précédentes.

Classeur Excel à télécharger
Pour la construction de cette formule audacieuse, nous suggérons d'appuyer les travaux sur un classeur hébergeant ces chaînes particulières.

• Télécharger le fichier codes-dans-chaines-de-textes.xlsx en cliquant sur ce lien,
• Double cliquer sur le classeur réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons bien les tableaux des chaînes et des codes. Mais bien sûr à ce stade, les formules d'extraction brillent encore par leur absence.

Solution dynamique
Ces chaînes alphanumériques peuvent évoluer en nombre. Les codes à chercher peuvent être plus ou moins nombreux. Nous souhaitons que les formules s'adaptent à ces variations. C'est la raison pour laquelle et par anticipation, nous avons construit des plages évolutives. Nous proposons de le constater.

• 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,

Le gestionnaire s'affiche et présente deux plages nommées respectivement chaines et codes, selon les syntaxes suivantes :

=DECALER(Codes!$C$5;;; NBVAL(Codes!$C:$C))
=DECALER(Codes!$F$5;;; NBVAL(Codes!$F:$F))

La première agit sur la colonne des chaînes alphanumériques. Elle exploite les fonctions Decaler et NbVal pour ajuster la hauteur de la plage à son contenu. Le principe de la seconde est identique. Mais elle agit sur la plage des codes à trouver.

• Cliquer sur le bouton Fermer du gestionnaire pour revenir sur la feuille Excel,

Naturellement, nous allons exploiter ces noms pour simplifier la construction de la formule matricielle.

Recherche des positions
Comme le raisonnement est assez particulier, nous proposons de procéder par étapes. Nous simplifierons ainsi grandement la compréhension, même si la formule à construire n'est finalement pas très complexe. Tout d'abord, nous souhaitons repérer les positions des chaînes porteuses des codes cherchés. La fonction de recherche dédiée est la fonction Equiv. Mais comme cette recherche doit se faire sur des fragments de chaînes et non sur les cellules complètes, nous allons exploiter le WilCard de l'astérisque dans son argument de recherche.

• Sélectionner les cellules d'extraction, soit la plage G5:G15,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Taper l'astérisque entre guillemets, soit : "*",
• Puis, ajouter le symbole de concaténation (&) pour réaliser l'assemblage avec les codes,
• Désigner la plage de ces codes par son nom, soit : codes,
• Taper de nouveau le symbole de concaténation suivi d'une étoile entre guillemets, soit : & "*",
• Inscrire un point-virgule (;) pour passer dans l'argument du tableau de recherche,
• Désigner la colonne des chaînes par son nom, soit : chaines,
• Taper un point-virgule suivi du chiffre zéro : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Enfin, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez l'apprécier, les résultats tombent. Ces numéros indiquent les positions des chaînes porteuses des codes respectifs. Les messages d'erreur (#N/A) sont naturels à ce stade. Comme vous le savez, une fonction de recherche, lorsqu'elle ne trouve pas, répond par une erreur. Et à partir de la cinquième ligne, nous ne fournissons plus aucun code à trouver. Nous neutraliserons ces messages plus tard.

Extraire les chaînes
Grâce à ces positions, nous allons maintenant pouvoir extraire facilement les chaînes embarquant les codes cherchés. Il suffit simplement d'imbriquer le précédent calcul dans la fonction d'extraction Index.

• Sélectionner de nouveau la plage de cellules G5:G15,
• Dans la barre de formule, adapter le précédent calcul comme suit :

=INDEX(chaines; EQUIV("*" & codes & "*"; chaines;0))

Il faut bien veiller à fermer la parenthèse de la fonction Index à la toute fin de la syntaxe.

• Puis, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Cette fois, nous obtenons bien l'extraction des chaînes situées sur les positions révélées par le calcul précédent.

Neutraliser les erreurs
Pour ne pas voir apparaître les retours d'erreurs lorsque la recherche n'est plus à faire, l'astuce est intéressante. Elle consiste à tester le calcul dans une fonction conditionnelle. S'il retourne une erreur, la cellule doit être gardée vide. Dans le cas contraire, il doit être déclenché. Donc la syntaxe de la formule d'extraction doit être répétée à deux reprises. Et la fonction Excel testant ce type d'erreur se nomme EstNa.

• Sélectionner de nouveau la plage de cellules G5:G15,
• Dans la barre de formule, adapter la précédente syntaxe comme suit :

=SI(ESTNA(INDEX(chaines; EQUIV("*" & codes & "*"; chaines; 0))); ""; INDEX(chaines; EQUIV("*" & codes & "*"; chaines; 0)))

• Puis, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Nous retrouvons bien les extractions précédentes mais les résultats sont plus propres. Les erreurs en queue de liste ont disparu. De plus, grâce à nos plages variables et dynamiques, si vous ajoutez un nouveau code à chercher, l'extraction correspondante se réalise parfaitement à la suite des autres.