Extraire les dates contenues dans 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 :


Extraire les dates des textes

C'est une astuce matricielle particulièrement brillante que nous souhaitons livrer dans ce nouvel opus. Il est question de dénicher les informations de dates encapsulées dans des phrases, pour être en mesure de reconstruire point par point, la date de l'événement mentionné et ce, par formules matricielles.



Sur l'exemple illustré par la capture, nous parvenons à trouver et à isoler indépendamment les informations sur le jour, le mois et l'année des dates mentionnées dans des événements cités. Dès lors, la reconstruction complète de la date finale devient un jeu d'enfants.

Classeur Excel à télécharger
Pour la construction de ces formules matricielles particulières, nous suggérons d'appuyer les travaux sur un classeur abritant des événements mentionnant des dates quelque peu éparpillées.

• Télécharger le classeur reconstruire-dates-dans-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 débouchons sur une feuille constituée de deux tableaux. Le premier n'est fait que d'une colonne. Quelques phrases relatent des événements en rappelant les dates respectives surlignées en orange. Mais comme vous pouvez le voir, aucune logique ou séquence remarquable ne permet de les identifier facilement aux yeux d'une formule d'extraction. Les emplacements sont très variables.

Sur la droite, un tableau de quatre colonnes se propose. Dans les trois dernières, il s'agit donc d'isoler le jour puis le mois et l'année de la date à détecter dans chaque événement du premier tableau. Dans la première colonne, une formule triviale existe : =F6 & " " & G6 & " " & H6. Elle consiste à recomposer la date complète en fonction des éléments de date individuellement extraits.

Extraire le mois de l'événement
Nous souhaitons commencer par isoler le mois de chaque événement en colonne G. Grâce à la fonction Index dans une matrice des 12 mois de l'année LIGNE($1:$12) retranscrits en tant que tels avec la Date recomposée dans la fonction Texte, nous pouvons exploiter la fonction Equiv pour rechercher la présence d'une occurrence semblable dans le texte de l'événement (B6). C'est le WildCard de l'astérisque qui va nous permettre de ne considérer que ce qui est placé avant et après la séquence remarquable, soit les lettres d'un mois.

• Cliquer sur la cellule G6 du premier mois à isoler, pour la sélectionner,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Inscrire la fonction de formatage suivie d'une parenthèse, soit : Texte(,
• Inscrire la fonction pour recomposer une date, suivie d'une parenthèse, soit : Date(,
• Taper un point-virgule (;) pour ignorer l'argument de l'année,

C'est en effet une matrice des 12 mois de l'année que nous souhaitons recomposer pour trouver des séquences similaires dans les événementsde la colonne B.

• Dans l'argument du mois, créer la matrice virtuelle suivante : Ligne($1:$12),

Les dollars sont importants pour que ces références ne suivent pas le déplacement de la formule que nous répliquerons ensuite sur les lignes du dessous. Grâce à la fonction Ligne embarquée dans un raisonnement matriciel, nous désignons donc les 12 mois d'une année. Ils seront ainsi tous passés en revue pour trouver une éventuelle correspondance dans chaque événement de la colonne B.

• Taper un point-virgule suivi du chiffre 1, soit : ;1,

Deux éléments au minimum sont effectivement nécessaires pour recomposer un fragment de date. Nous nous basons donc sur le premier jour de chaque mois, mais nous ne retiendrons que le mois, grâce à un format adapté à passer en second argument de la fonction Texte.

• Fermer la parenthèse de la fonction Date,
• Puis, taper un point-virgule (;) pour passer dans le second argument de la fonction Texte,
• Dès lors, construire le format suivant entre guillemets : "mmmm",

Avec une telle précision, c'est l'information sur le mois complet que nous recomposons et que nous souhaitons trouver.

• Fermer la parenthèse de la fonction Texte,
• Puis, taper un point-virgule (;) pour passer dans l'argument de ligne de la fonction Index,

Nous allons maintenant exploiter la fonction Equiv dans une utilisation à contre-courant pour trouver une occurrence semblable de date, soit de l'un des mois de l'année, dans la cellule de l'événement.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Puis, taper le chiffre 1 en guise de valeur cherchée,

Ce chiffre fait office de booléen. Grâce à lui, si un mois est trouvé dans la matrice construite en premier paramètre de la fonction Index, la fonction Equiv répondra favorablement. Et c'est ainsi que le mois en question pourra être extrait par la matrice virtuelle.

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

Dans ce raisonnement particulier, ce tableau de recherche n'est autre qu'une question. Est-ce que l'une des séquences remarquables d'un mois est trouvée dans la cellule de l'événement. Si le dénombrement est positif, il répondra favorablement pour la fonction Equiv qui retournera le mois concordant à la position observée dans la matrice virtuelle.

• Inscrire la fonction de dénombrement suivie d'une parenthèse, soit : Nb.Si(,
• Désigner le premier événement à analyser en cliquant sur sa cellule B6,
• Puis, taper un point-virgule (;) pour passer dans l'argument du critère,

Ce critère n'est autre que cette même matrice virtuelle des mois encadrée du WilCard de l'astérisque. Peu importe ce qu'il y a avant ou après, en confrontant les mois des deux matrices, l'idée est de retrouver cette même séquence dans chaque chaîne des événements.

• Taper le symbole de l'astérisque entre guillemets, soit : "*",
• Ajouter le symbole de concaténation (&) pour commencer l'assemblage,
• Répliquer toute la syntaxe de la matrice virtuelle des mois, soit :

Texte(Date(;Ligne($1:$12); 1); "mmmm")

• Ajouter un nouveau symbole de concaténation suivi de l'astérisque entre guillemets : & "*",
• Fermer la parenthèse de la fonction Nb.Si,
• Taper un point-virgule suivi du chiffre zéro : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Fermer la parenthèse de la fonction Index,
• Puis, valider la formule matricielle par le raccourci clavier CTRL + MAJ + Entrée,

Comme vous pouvez l'apprécier, le mois de la date du premier événement est parfaitement extrait en cellule G6. Et dans le même temps, il est naturellement répliqué à l'identique en cellule E6, dont la formule attend les autres éléments de date pour la reconstruire intégralement.

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

Ce sont bien tous les mois de chaque événement que nous sommes ainsi parvenus à isoler individuellement, grâce à une unique formule matricielle dont la syntaxe complète est la suivante :

{ =INDEX(TEXTE(DATE(; LIGNE($1:$12);1); "mmmm"); EQUIV(1; NB.SI(B6; "*" & TEXTE(DATE(;LIGNE($1:$12); 1); "mmmm") & "*"); 0))}

Certes elle est relativement complexe, mais des bouts de syntaxe assez longs se répètent. De plus, en l'analysant minutieusement, elle est d'une logique implacable.



Extraire l'année de l'événement
Pour extraire les années des événements, le principe est le même, mais nous devons nous baser sur une matrice des années transformées en dates. Et arbitrairement, nous prenons de la marge en choisissant la plage 1900 à 2030.

• En cellule H6, construire la formule matricielle suivante :

=INDEX(TEXTE(DATE(LIGNE($1900:$2030);; 1); "aaaa"); EQUIV(1; NB.SI(B6; "*" & TEXTE(DATE(LIGNE($1900:$2030); ; 1); "aaaa") & "*"); 0))

Naturellement, la matrice virtuelle intervient cette fois en premier argument de la fonction Date pour les années. Le format change lui aussi (aaaa) pour récupérer une information de date chiffrée sur les quatre numéros qui composent une année.

• Valider cette formule matricielle avec le raccourci clavier CTRL + MAJ + Entrée,
• Puis, double cliquer sur la poignée du résultat pour la répliquer sur les autres lignes,

Comme vous pouvez le voir, nous parvenons à parfaitement isoler les années des dates mentionnées dans les événements respectifs.

Extraire le jour de l'événement
Pour extraire les jours des dates des événements, le problème se corse a priori. La séquence remarquable des deux chiffres successifs est aussi présente dans les années. La formule matricielle va donc d'abord extraire le nombre trouvé le plus petit des deux. Nous pouvons néanmoins proposer une solution intermédiaire. Elle consiste à purger la chaîne de l'événement de son information sur l'année désormais connue puisqu'extraite. Pour cela, nous pouvons par exemple agir en colonne annexe J, avec la fonction Substitue : =SUBSTITUE(B6;H6;""). Dès lors, sur ce résultat, il n'existe plus de conflit possible, puisque l'année a disparu. Il ne nous reste donc plus qu'à agir sur cette colonne J, mais cette fois avec une matrice des 31 jours potentiels qui composent un mois :

=INDEX(TEXTE(DATE(1; ; LIGNE($1:$31)); "jj"); EQUIV(1; NB.SI(J6; "*" & TEXTE(DATE(1; ; LIGNE($1:$31));"jj") & "*"); 0))

Cependant et pour être plus directs, nous pouvons aussi exploiter une technique que nous avions découverte pour extraire les chiffres des textes en isolant la première séquence de deux chiffres consécutifs. Et c'est la solution que nous proposons de redécouvrir ici.

• Sélectionner la case du premier jour à extraire en cliquant sur la cellule F6,
• 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 événement en cliquant sur sa cellule B6,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la position de départ,
• Inscrire la fonction de recherche de position suivie d'une parenthèse, soit : Equiv(,

L'idée est de découper l'événement en B6 à la recherche des deux premiers chiffres. Sur une longueur suffisante, nous allons analyser les lettres de la phrase par paires pour vérifier s'il ne s'agit pas d'un assemblage numérique. Donc la fonction logique EstNum est essentielle dans une utilisation booléenne dérivée de la fonction Equiv.

• Inscrire le booléen Vrai en guise d'élément de recherche,
• Taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

Dans ce tableau de recherche et dans ce raisonnement matriciel récursif, ce sont tous les caractères potentiels de la chaîne qui doivent être analysés par paires par la fonction EstNum.

• Inscrire la fonction de test logique suivie d'une parenthèse, soit : EstNum(,
• Inscrire de nouveau la fonction de découpe suivie d'une parenthèse, soit : Stxt(,
• Désigner une fois encore le premier événement en cliquant sur sa cellule B6,
• Dès lors, taper un point-virgule (;) pour passer dans l'argument de la position de départ,

Pour que les paires soient toutes analysées, nous devons faire varier la position de l'analyse du premier au dernier caractère. Mais, nous ne connaissons pas la longueur du texte. C'est la raison pour laquelle nous proposons de construire une matrice virtuelle de positions, suffisamment grande.

• Construire la matrice virtuelle suivante : Ligne($1:$200),

Comme toujours, les dollars($) sont importants pour la réplication. La découpe récursive va donc débuter à partir de la première lettre puis à partir de la deuxième jusqu'à la deux centièmes si d'aventure notre phrase est aussi longue. Et à partir de chacune de ces positions de départ, nous devons entreprendre une découpe sur deux caractères pour que la fonction EstNum puisse les analyser. C'est précisément l'information qu'attend le prochain paramètre de la fonction Stxt.

• Taper un point-virgule (;) pour passer dans l'argument de la longueur de découpe,
• Inscrire le chiffre 2 pour prélever deux caractères au fur et à mesure de la progression,
• Fermer la parenthèse de la fonction Stxt,

La fonction Stxt est une fonction de découpe agissant sur des textes. Donc même une chaîne numérique isolée est considérée comme un texte à ce stade. Nous devons forcer sa conversion. Pour cela et comme nous en avons l'habitude, nous allons engager une multiplication.

• Multiplier la paire résultante par le chiffre 1, soit : *1,
• Fermer la parenthèse de la fonction EstNum,

De fait, nous sommes de retour dans les bornes de la fonction Equiv.

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

De cette manière, nous nous déplaçons d'une unité vers la droite (+1) pour sauter l'espace de la position repérée par la première fonction Stxt et nous prélevons ainsi uniquement les deux chiffres (2).

• Taper un point-virgule suivi du chiffre 2 : ;2, pour prélever ces deux chiffres découverts,
• Fermer la parenthèse de la première fonction Stxt,
• Valider la formule par le raccourci clavier CTRL + MAJ + Entrée,
• Puis, double cliquer sur la poignée du résultat pour répliquer la logique,

Comme vous le constatez, nous avons parfaitement réussi à isoler toutes les informations de dates au milieu des chaînes de textes pour les reconstruire toutes intégralement.