Tutoriel Regex Excel : maîtriser la recherche de motifs avec les expressions régulières

Utiliser les RegEx dans Excel

Bien qu’Excel ne prenne pas en charge les RegEx nativement, vous pouvez les activer en écrivant votre propre fonction via un script VBA (sous Windows) ou en installant des compléments (sur Mac). Voyons comment activer une fonction regex pour extraire du texte depuis une colonne dans Excel.

Pour Windows

Pour commencer, appuyez sur Alt+F11 pour ouvrir l’éditeur Visual Basic. Vous pouvez aussi y accéder via Outils > Macro > Éditeur Visual Basic. Puis cliquez sur Insertion > Module.

Collez la fonction suivante dans le module :

Public Function CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
    Dim textMatches() As String
    Dim matchesIndex As Integer
    Dim regex As Object
    Dim matches As Object
    
    On Error GoTo ErrHandler
    
    CustomRegExpExtract = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

Enregistrez la fonction et revenez à la feuille de calcul. Enregistrez ensuite le classeur en tant que fichier activé pour les macros, avec l’extension .xlsm.

Utiliser la fonction dans une feuille Excel

Après avoir activé les macros dans votre classeur, vous pouvez utiliser la fonction CustomRegExpExtract comme une fonction classique d’Excel pour analyser des motifs complexes, où :

• inputText est la chaîne d’origine à analyser ;

• regexPattern est le motif d’expression régulière utilisé pour analyser inputText ;

• instanceNumber renvoie par défaut toutes les occurrences du motif, mais vous pouvez indiquer un entier pour préciser le rang d’occurrence à retourner ; et

• matchCase précise s’il faut tenir compte de la casse pendant la recherche (TRUE ou omis) ou l’ignorer (FALSE).

Imaginons que nous souhaitions extraire des numéros de téléphone depuis un champ. Nous pouvons saisir le motif regex dans n’importe quelle cellule (dans l’exemple ci‑dessous, il se trouve en A2). En A6, votre chaîne libre d’exemple : « Sophia, 1111111111 ». En B6, nous tapons =CustomRegExpExtract(A6,$A$2). La fonction extrait le numéro en comparant la chaîne au motif regex en A2 et renvoie « 1111111111 », comme prévu.

Pour Mac OS

Malheureusement, Mac OS ne reconnaît pas la bibliothèque MS VBScript Regular Expressions 5.5. Pour activer la recherche de motifs complexes dans Excel sur Mac, deux options : utiliser les filtres avancés ou installer des compléments.

Utiliser les filtres avancés

Les filtres avancés d’Excel permettent d’effectuer des opérations de filtrage complexes à partir de plusieurs critères. Cette fonctionnalité est particulièrement utile avec de grands jeux de données. Voici comment les utiliser :

• Préparer vos données : assurez‑vous que vos données sont présentées sous forme de tableau avec des en‑têtes clairs.

• Créer une zone de critères : créez une zone distincte dans la feuille pour définir les critères. Elle doit reprendre les mêmes en‑têtes que les colonnes à filtrer. Sous ces en‑têtes, indiquez les critères. Vous pouvez saisir plusieurs critères sous le même en‑tête pour une logique « OU », ou sur des lignes séparées pour une logique « ET ».

• Sélectionner les données : cliquez sur une cellule de votre jeu de données, ou sélectionnez toute la plage à filtrer.

• Configurer le filtre : cliquez sur l’onglet Données du ruban puis sur l’option Filtre avancé. Cliquez sur Avancé dans le groupe Trier et filtrer. Dans la boîte de dialogue, choisissez de filtrer la liste sur place ou de copier le résultat ailleurs. Indiquez la plage de liste (vos données) et la zone de critères (vos conditions). Cliquez sur OK pour appliquer le filtre.

Supposons que vous ayez un jeu de données avec les colonnes Name, Age, City. Vous souhaitez n’afficher que les personnes de plus de 30 ans à New York, ou toute personne à Los Angeles. Votre zone de critères pourrait ressembler à ceci :

Installer des compléments

• Kutools for Excel : Kutools est un complément complet qui inclut de nombreuses fonctionnalités, dont la recherche/remplacement avec regex. Il ajoute un nouvel onglet au ruban Excel avec un large éventail d’outils, y compris des fonctions regex.

• RegEx Find/Replace : ce complément pour Excel permet de rechercher et remplacer du texte à l’aide d’expressions régulières. Il s’intègre à Excel et offre une fonction de recherche/remplacement plus avancée que l’option native.

• PowerGREP : bien que n’étant pas un complément Excel à proprement parler, PowerGREP peut travailler avec des fichiers Excel. C’est un outil grep puissant pour Windows qui permet des opérations regex complexes sur des fichiers texte et binaires. Il s’agit plutôt d’une application autonome, mais elle peut être utilisée avec Excel pour un traitement regex avancé. Son prix est élevé : une licence utilisateur coûte 159 $ et grimpe à 5 300 $ pour 100 utilisateurs.

Certains compléments sont accessibles directement dans Excel via l’onglet Insertion > Obtenir des compléments.

D’autres doivent être téléchargés depuis une source externe ; suivez alors leurs instructions d’installation.

Quelques points d’attention :

• Compatibilité : vérifiez la compatibilité du complément avec votre version d’Excel et votre système d’exploitation.
• Sécurité : ne téléchargez et n’installez des compléments que depuis des sources fiables.
• Besoins fonctionnels : identifiez les tâches regex spécifiques à réaliser et choisissez le complément le plus adapté.

Conclusion

Dans la plupart des cas, les fonctions natives comme TROUVE, CHERCHE et REMPLACER couvrent 90 % des besoins de manipulation de texte dans Excel. Pour les 10 % restants, lorsqu’il s’agit de motifs plus complexes, les regex rendent d’immenses services. Pour en savoir plus sur l’application des expressions régulières dans des langages populaires, découvrez nos cours Regular Expressions in Python et Intermediate Regular Expressions in R.

Pour aller plus loin avec Excel, découvrez notre parcours de compétences Excel Fundamentals, qui vous guide à travers les compétences essentielles pour maîtriser l’outil.