Extraire du texte avec une regex dans Excel

Extraire la première correspondance avec REGEX.EXTRAIRE

Pour extraire la première correspondance, REGEX.EXTRAIRE n’a besoin que de ses deux premiers arguments, le texte et le motif. Elle lit le texte de gauche à droite et renvoie la première référence qu’elle rencontre. Sur la ligne 3, le client relance pour deux commandes, CMD-1057 et CMD-1061, mais la formule ne garde que la première.

Réunir toutes les correspondances d’une regex dans une cellule

Pour réunir toutes les correspondances dans une cellule, commence par passer à 1 le troisième argument, mode_renvoi, pour que REGEX.EXTRAIRE rende toutes les références du message. Laissée seule, la fonction les répartit alors dans les cellules situées à sa droite. JOINDRE.TEXTE les assemble au contraire dans la cellule de la formule, avec le séparateur de ton choix, ici une virgule suivie d’une espace.

Comme cette formule ne déborde plus, tu peux la recopier vers le bas, une ligne par message, sans rien écraser. En mode 1 seul, REGEX.EXTRAIRE a besoin d’autant de cellules vides à sa droite qu’il y a de références, et renvoie l’erreur #PROPAGATION! dès que l’une d’elles est occupée.

Ne garder que le numéro, par un groupe de capture

Pour ne garder que le numéro, entoure \d{4} de parenthèses, qui en font un groupe de capture, puis passe au mode 2. REGEX.EXTRAIRE renvoie alors le contenu des groupes au lieu de la correspondance entière. Ce mode ne lit toutefois que la première correspondance, si bien que la formule ci-dessous renvoie 1057 et laisse 1061 de côté.

Quand tu veux tous les numéros du message, change plutôt de motif. Le regard en arrière (?<=CMD-) exige que CMD- précède les chiffres sans l’inclure dans le résultat, si bien que le mode 1 renvoie chaque numéro seul. JOINDRE.TEXTE les réunit ensuite comme plus haut, séparés cette fois par une barre oblique.

Ignorer les majuscules avec respect_casse

Pour ignorer les majuscules, passe à 1 le quatrième argument, respect_casse, faute de quoi le motif distingue les majuscules des minuscules. Sans respect_casse, la formule ne rend que CMD-1108 sur la ligne 5, où le client a tapé sa première référence en minuscules, cmd-1102.

Éviter #N/A quand la regex ne trouve rien

Pour éviter #N/A, entoure la formule de SIERREUR, qui remplace l’erreur par une cellule vide. REGEX.EXTRAIRE renvoie en effet #N/A dès que le motif ne trouve rien, comme sur la ligne 4, où le client ne fait que remercier pour l’échange. SIERREUR se pose de la même façon autour de JOINDRE.TEXTE, qui transmet l’erreur.

Remplir la colonne par l’exemple avec Ctrl+E

Avec Ctrl+E, le Remplissage instantané complète la colonne d’après un exemple, sans que tu écrives de formule. Il déduit une règle de ce que tu tapes à côté des données, puis l’applique au reste de la colonne. Tu lui donnes donc d’abord la référence du premier message dans la colonne voisine, et il se charge seul des lignes suivantes.

1. 01

   En B2, tape la référence du premier message, CMD-1043.

2. 02

   Sélectionne B2:B7.

3. 03

   Appuie sur Ctrl+E, ou clique sur Remplissage instantané dans l’onglet Données.

Sur des messages rédigés librement, ce seul exemple ne lui suffit pourtant pas. À partir de CMD-1043, il écrit CMD-1061 sur la ligne 3, soit la deuxième référence au lieu de la première. Il écrit ensuite Merci sur la ligne 4, puis des bouts de phrase sur les lignes 5 à 7. Tu dois donc relire chaque ligne, et les valeurs qu’il laisse restent figées, sans suivre un message corrigé après coup.

Extraire une référence par formule avec STXT et CHERCHE

Pour extraire une référence par formule, CHERCHE trouve la position de CMD- dans le message, puis STXT en prend huit caractères à partir de là, soit la longueur d’une référence. La formule rend ainsi CMD-1043 sur la ligne 2, et la première référence sur les lignes qui en citent plusieurs.

Sur la ligne 4, où le client ne cite aucune commande, CHERCHE ne trouve rien et la formule renvoie #VALEUR!, que SIERREUR peut remplacer par une cellule vide. Sur la ligne 5, CHERCHE, qui ignore les majuscules, s’arrête sur cmd-1102, alors que TROUVE, qui les respecte, passe directement à CMD-1108. Et pour réunir toutes les références d’un message sans REGEX.EXTRAIRE, il te faut la fonction de la partie suivante.

Écrire la formule REGEX_EXTRAIRE et ses cinq arguments

Pour écrire la formule, seuls les deux premiers des cinq arguments sont obligatoires, le texte et le motif, dans le même ordre que pour REGEX.EXTRAIRE. Les trois autres ont une valeur par défaut, si bien que =REGEX_EXTRAIRE(A2;"CMD-\d{4}") suffit à renvoyer CMD-1043. Sur un message qui cite plusieurs références, c’est alors une virgule suivie d’une espace qui les sépare.

Choisir le séparateur et le groupe à extraire

Le troisième argument choisit le groupe à extraire et le quatrième le séparateur. Ce sont eux qui remplissent la colonne C de l’exemple, où chaque numéro perd son préfixe CMD-. Le groupe 1 y renvoie le contenu des parenthèses de chaque correspondance, et une barre oblique entourée d’espaces remplace la virgule entre deux numéros. Contrairement au mode 2 de REGEX.EXTRAIRE, qui s’arrête à la première correspondance, REGEX_EXTRAIRE applique ainsi le groupe à chacune.

Le groupe choisi doit toutefois exister dans le motif, sans quoi la fonction renvoie #VALEUR!. Le groupe 2 renvoie ainsi #VALEUR! sur CMD-(\d{4}), qui n’a qu’une paire de parenthèses, et un groupe négatif fait de même. Une correspondance vide n’est en revanche jamais ajoutée, si bien qu’un groupe facultatif resté vide ne laisse pas deux séparateurs à la suite.

Rendre la regex insensible à la casse

Pour rendre la regex insensible à la casse, passe le cinquième argument à VRAI, sans toucher au motif. La fonction trouve alors aussi cmd-1102, la référence que le client de la ligne 5 a tapée en minuscules. Sans cet argument, REGEX_EXTRAIRE distingue les majuscules des minuscules, comme REGEX.EXTRAIRE, et ne rend sur cette ligne que CMD-1108.

Installer REGEX_EXTRAIRE dans le classeur

Utiliser une regex en VBA depuis une macro

Une regex s’utilise aussi en VBA, puisque REGEX_EXTRAIRE, une fois collée dans un module, s’appelle depuis une macro du même classeur comme n’importe quelle fonction VBA. La macro ci-dessous s’en sert pour remplir la colonne B de l’exemple sans y laisser la moindre formule. Et pour écrire ta propre regex en VBA, tu déclares le même objet que la fonction, VBScript.RegExp.

Sub AfficherReferences()
' Écrit dans la colonne B les références de chaque message de la colonne A.
    Dim ligne As Long
    For ligne = 2 To 7
        Cells(ligne, 2).Value = REGEX_EXTRAIRE(Cells(ligne, 1).Value, "CMD-\d{4}")
    Next ligne
End Sub

Ne compte pas, en revanche, sur les fonctions regex de Microsoft 365 dans WorksheetFunction, qui répond par l’erreur 438 quand une macro y cherche la fonction d’extraction sous son nom anglais. Evaluate, qui calcule une formule écrite en anglais, les atteint pourtant, et rend ainsi CMD-1057 sur le message de la ligne 3.