tester - Comment utiliser les expressions régulières(Regex) dans Microsoft Excel à la fois dans la cellule et les boucles




tester expression régulière (4)

Comment puis-je utiliser des expressions régulières dans Excel et profiter de la puissante configuration de grille d'Excel pour la manipulation des données?

  • Fonction dans la cellule pour renvoyer le motif correspondant ou la valeur remplacée dans la chaîne.
  • Sub pour parcourir une colonne de données et extraire des correspondances avec des cellules adjacentes.
  • Quelle configuration est nécessaire?
  • Quels sont les caractères spéciaux d'Excel pour les expressions Regex?

Je comprends que Regex n'est pas idéal pour de nombreuses situations ( utiliser ou ne pas utiliser les expressions régulières? ) Car excel peut utiliser les commandes de type Left , Mid , Right , Instr pour des manipulations similaires.


@patszim: Très utile, merci. Ajout d'une fonction regex_subst () . Exemples:

=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo

Voici le code simplifié (plus simple pour moi, de toute façon). Je ne pouvais pas comprendre comment construire un modèle de sortie approprié en utilisant ce qui précède pour fonctionner comme mes exemples:

Function regex_subst( _
     strInput As String _
   , matchPattern As String _
   , Optional ByVal replacePattern As String = "" _
) As Variant
    Dim inputRegexObj As New VBScript_RegExp_55.RegExp

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With

    regex_subst = inputRegexObj.Replace(strInput, replacePattern)
End Function

Expansion de la answer pour ceux qui sont pressés.

  1. Ouvrez le classeur Excel.
  2. Alt + F11 pour ouvrir la fenêtre VBA / Macros.
  3. Ajouter une référence à regex sous Outils puis références
  4. et en sélectionnant Microsoft VBScript Regular Expression 5.5
  5. Insérez un nouveau module (le code doit résider dans le module sinon il ne fonctionne pas).
  6. Dans le module nouvellement inséré,
  7. ajoutez le code suivant:

    Function RegxFunc(strInput As String, regexPattern As String) As String
        Dim regEx As New RegExp
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .pattern = regexPattern
        End With
    
        If regEx.Test(strInput) Then
            Set matches = regEx.Execute(strInput)
            RegxFunc = matches(0).Value
        Else
            RegxFunc = "not matched"
        End If
    End Function
    
  8. Le motif regex est placé dans l'une des cellules et le référencement absolu y est utilisé. La fonction sera liée au classeur dans lequel elle a été créée.
    Si vous devez l'utiliser dans différents classeurs, stockez la fonction dans Personal.XLSB.


Je ne suis pas un expert comme vous les gens, même alors, voici mon chemin.

Function RegParse(ByVal pattern As String, ByVal html As String)
    Dim regex   As RegExp
    Set regex = New RegExp

    With regex
        .IgnoreCase = True  'ignoring cases while regex engine performs the search.
        .pattern = pattern  'declaring regex pattern.
        .Global = False     'restricting regex to find only first match.

        If .Test(html) Then         'Testing if the pattern matches or not
            mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
            RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
        Else
            RegParse = "#N/A"
        End If

    End With
End Function

Pour utiliser des expressions régulières directement dans les formules Excel, la fonction UDF suivante (fonction définie par l'utilisateur) peut être utile. Il expose plus ou moins directement la fonctionnalité d'expression régulière en tant que fonction Excel.

Comment ça marche

Cela prend 2-3 paramètres.

  1. Un texte pour utiliser l'expression régulière sur.
  2. Une expression régulière.
  3. Une chaîne de format spécifiant l'aspect du résultat. Il peut contenir $0 , $1 , $2 , et ainsi de suite. $0 correspond à la totalité du match, $1 et supérieur correspondent aux groupes de correspondance respectifs dans l'expression régulière. Par défaut à $0 .

Quelques exemples

Extraire une adresse email:

=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+", "$0")

Résultats dans: [email protected]

Extraire plusieurs sous-chaînes:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Résultats par: E-Mail: [email protected], Name: Peter Gordon

Pour séparer une chaîne combinée dans une seule cellule en ses composants dans plusieurs cellules:

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

Résultats à: Peter Gordon [email protected] ...

Comment utiliser

Pour utiliser cette UDF, procédez comme suit (en gros sur la base de cette page Microsoft : ils contiennent de bonnes informations supplémentaires!):

  1. Dans Excel dans un fichier Macro activé ('.xlsm'), appuyez sur ALT+F11 pour ouvrir l'éditeur Microsoft Visual Basic pour Applications .
  2. Ajouter une référence VBA à la bibliothèque Regular Expressions (copiée sans vergogne à partir de Portland Runners ++ answer ):
    1. Cliquez sur Outils -> Références (excusez la capture d'écran allemande)
    2. Recherchez Microsoft VBScript Regular Expressions 5.5 dans la liste et cochez la case en regard.
    3. Cliquez sur OK
  3. Cliquez sur Insérer un module . Si vous donnez un nom différent à votre module, assurez-vous que le module n'a pas le même nom que l'UDF ci-dessous (par exemple, nommer le module Regex et la fonction regex provoque des erreurs #NAME! ).

  4. Dans la grande fenêtre de texte au milieu, insérez ce qui suit:

    Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
        Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
        Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
        Dim replaceNumber As Integer
    
        With inputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
        With outputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)"
        End With
        With outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        End With
    
        Set inputMatches = inputRegexObj.Execute(strInput)
        If inputMatches.Count = 0 Then
            regex = False
        Else
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            For Each replaceMatch In replaceMatches
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$" & replaceNumber
    
                If replaceNumber = 0 Then
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Else
                    If replaceNumber > inputMatches(0).SubMatches.Count Then
                        'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Exit Function
                    Else
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                End If
            Next
            regex = outputPattern
        End If
    End Function
    
  5. Enregistrez et fermez la fenêtre Microsoft Visual Basic pour Applications Editor.







excel-vba