Bonjour à tous,

Je vous invite à une petite discussion sur 3 nouvelles fonctions disponibles en insider, et aussi sur comment faire la même chose en VBA (donc pour toutes les versions d'excel !).

Microsoft vient d'annoncer en bêta (Insider) trois nouvelles fonctions qui vont permettre de gérer les REGEX ou encore expression régulière. La traduction française n'est pas encore disponible, mais au moins on peut déjà utiliser les versions anglaises.

Le fichier est disponible ici.
Ne pas oublier le clic droit, propriétés pour le déverrouiller.

En anglais, on a donc 3 fonctions :
REGEXTEST, REGEXREPLACE et REGEXEXTRACT

J'imagine qu'en Français, on aura ceci :

• TESTER.REGEX
• REMPLACER.REGEX
• EXTRAIRE.REGEX

Comme ces fonctions sont encore en béta-test, je vais juste donner quelques exemples simples, mais elles ont l'air très puissantes.

Exemple classique : le code postal dans une adresse.
La règle implicite est 5 chiffres qui se suivent. Par exemple, d pour décimal et { } pour indiquer la fréquence. Voici les deux formules. La première est disponible en mode insider, et la deuxième est liée au code VBA du fichier téléchargeable plus haut.

=REGEXEXTRACT(A2;"\d{5}")
=fctEXTRAIRE_REGEX(A2;"\d{5}")

Bref, les expressions régulières existent déjà depuis très très très longtemps dans Excel, en VBA. Il suffit d'ajouter une référence dans le VBE.

Et ensuite, on a accès à pleins de choses.

Mon post favori sur ce sujet est la réponse à la première réponse (en Anglais) de ce lien. C'est une mine d'or, qui va directement à l'essentiel.

https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

En attendant Excel, voici comment faire en VBA

Voici un code VBA d'une fonction quasi-équivalente à REGEXEXTRACT (merci au lien ci-dessus et à ChatGPT pour l'aide à l'écriture de cette fonction). On peut encore beaucoup améliorer, mais je pense que c'est suffisant pour 90% des usages.
Pour l'utiliser, voici les arguments :

• la source,
• l'expression REGEX, et
• (optionnel) 0 ou 1 pour retourner la première valeur ou la liste des valeurs trouvées.

Par exemple : =fctEXTRAIRE_REGEX(A2;"\d{5}";1) pour extraire tous les codes postaux à 5 chiffres.
Note : je n'ai pas testé le retour de plusieurs résultats dans les anciennes versions d'Excel sans gestion de la propagation.

Function fctEXTRAIRE_REGEX(sSource As String, sRegex As String, Optional iChoix As Integer) As Variant
    ' Ajoutez une référence à Microsoft VBScript Regular Expressions (Menu: Tools > References > Cochez "Microsoft VBScript Regular Expressions 5.5")
    
    ' Rendre la fonction volatile pour recalculer à chaque modification
    ' Cette option peut ralentir les calculs sur de gros fichiers, donc a utiliser en fonction du contexte
    Application.Volatile

    ' Définir un objet de type RegExp
    Dim regEx As New RegExp
    regEx.Global = True
    regEx.MultiLine = True ' Permettre la correspondance sur plusieurs lignes
    regEx.IgnoreCase = False ' Sensible à la casse, peut être ajusté selon les besoins
    regEx.Pattern = sRegex

    ' Vérification et traitement basé sur le choix
    
    ' Retourner le premier résultat
    On Error Resume Next ' Gérer les erreurs pour éviter les plantages si aucune correspondance n'est trouvée
    fctEXTRAIRE_REGEX = regEx.Execute(sSource)(0).Value ' Retourne la première valeur trouvée
    On Error GoTo 0 ' Réinitialiser la gestion des erreurs
    If IsEmpty(fctEXTRAIRE_REGEX) Then
        fctEXTRAIRE_REGEX = CVErr(xlErrNA) ' Si pas de correspondance, retourner la valeur d'erreur N/A
        Exit Function
    End If
    If iChoix = 0 Then
        'on ne fait rien, la valeur est deja retournée
    Else
        Dim matches As Object
        Dim match As Object
        Dim aResults() As String
        Dim i As Integer
        
        ' Exécution du regex sur la source
        Set matches = regEx.Execute(sSource)
        
        ' Si aucune correspondance n'est trouvée, retourner un tableau vide
        If matches.Count = 0 Then
            fctEXTRAIRE_REGEX = Array()
            Exit Function
        End If

        ' Redimensionner le tableau pour contenir les résultats
        ReDim aResults(matches.Count - 1)
        
        ' Boucler à travers les correspondances et remplir le tableau
        i = 0
        For Each match In matches
            aResults(i) = match.Value
            i = i + 1
        Next match
        
        ' Retourner le tableau de résultats
        fctEXTRAIRE_REGEX = aResults
    End If
End Function

Autre exemple : le numéro de téléphone
La règle implicite est un format du style 99 99 99 99 99

=REGEXEXTRACT(A6;"\d{2} \d{2} \d{2} \d{2} \d{2}")
=fctEXTRAIRE_REGEX(A8;"\d{2} \d{2} \d{2} \d{2} \d{2}")

Ou encore, si on veut s'amuser un peu :

=REGEXEXTRACT(A7;"(\d{2} ){4}\d{2}")

Gestion de plages multiples en entrée et sortie.

La fonction peut s'utiliser sur une plage en entrée :

Le résultat peut être propagé en mettant 1 comme troisième argument. Ici on propage sur B et C

L'expression ne trouve rien

Dans ce cas, Excel va retourner #N/A. On peut capturer cette erreur avec la fonction SI.NON.DISP.

[Mode râleur ON] Je ne comprendrai jamais pourquoi on a ESTNA et pas SI.NA. Cela aurait été tellement plus logique et simple. Bref.[Mode râleur OFF]

Évidemment, ce sont des Regex un peu simple, mais déjà vous pouvez déjà voir la puissance de ce langage.

Dans la vraie vie, je les ai utilisées pour éclater une bibliographie et séparer les auteurs, dates de publications, titre de l'article, titre du journal, etc.

Ce qui est très bien aussi, c'est que la base de connaissance internet sur les Regex sur internet est excellente, donc les GenAI (e.g. ChatGPT) sont déjà très bien entrainées pour ce type de problèmes.

Je vous souhaite de beaucoup vous amuser avec ce fichier !

S'il y a suffisamment d'intérêt, je vais aussi écrire la fonction pour les anciennes versions d'excel, et ajouter les fonctions de test et de remplacement.

N'hésitez-pas à partager vos trouvailles et cas d'usage.

A bientôt