683 votes

Comment utiliser les expressions régulières (Regex) dans Microsoft Excel, aussi bien dans les cellules que dans les boucles.

Comment utiliser les expressions régulières dans Excel et tirer parti de la puissante configuration de type grille d'Excel pour la manipulation des données ?

  • Fonction intracellulaire permettant de renvoyer le modèle correspondant ou la valeur remplacée dans une chaîne de caractères.
  • Sub pour parcourir en boucle une colonne de données et extraire les correspondances avec les cellules adjacentes.
  • Quelle est la configuration 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 ? ) puisque excel peut utiliser Left , Mid , Right , Instr pour des manipulations similaires.

11 votes

Je recommande vivement cet article sur les Regexp en VB/VBA par Patrick Matthews

1 votes

Essayez cet add-in gratuit : seotoolsforexcel.com/regexpfind

3 votes

N'oublions pas les Like opérateur qui fournit une sorte de version légère de la fonctionnalité de style regex. Elle est généralement beaucoup plus rapide que la regex, même si elle est enveloppée dans une procédure de sous-fonction ou de fonction.

1062voto

Portland Runner Points 4941

Expressions régulières sont utilisés pour la correspondance de motifs. Pour les utiliser dans Excel, procédez comme suit.

Étape 1 : Ajouter une référence VBA à "Microsoft VBScript Regular Expressions 5.5".

  • Sélectionnez l'onglet "Développeur" ( Je n'ai pas cet onglet, que dois-je faire ? )
  • Sélectionnez l'icône "Visual Basic" dans la section du ruban "Code".
  • Dans la fenêtre "Microsoft Visual Basic for Applications", sélectionnez "Outils" dans le menu supérieur.
  • Sélectionnez "Références".
  • Cochez la case en regard de "Microsoft VBScript Regular Expressions 5.5" pour l'inclure dans votre classeur.
  • Cliquez sur "OK".

Étape 2 : Définissez votre modèle

Définitions de base :

- Gamme.

  • Par exemple a-z correspond à une lettre minuscule de a à z
  • Par exemple 0-5 correspond à n'importe quel nombre de 0 à 5

[] Faites correspondre exactement un des objets entre ces crochets.

  • Par exemple [a] correspond à la lettre a
  • Par exemple [abc] correspond à une seule lettre qui peut être a, b ou c
  • Par exemple [a-z] correspond à n'importe quelle lettre minuscule de l'alphabet.

() Regroupe les différentes correspondances à des fins de retour. Voir les exemples ci-dessous.

{} Multiplicateur pour les copies répétées du motif défini avant lui.

  • Par exemple [a]{2} correspond à deux lettres minuscules a consécutives : aa
  • Par exemple [a]{1,3} correspond à au moins une et jusqu'à trois lettres minuscules a , aa , aaa

+ Correspond à au moins un, ou plusieurs, des motifs définis avant lui.

  • Par exemple a+ correspondra à des a consécutifs a , aa , aaa et ainsi de suite

? Correspond à zéro ou un des motifs définis avant lui.

  • Par exemple, un motif peut être présent ou non, mais ne peut être apparié qu'une seule fois.
  • Par exemple [a-z]? correspond à une chaîne vide ou à une seule lettre minuscule.

* Correspond à zéro ou plus du motif défini avant lui. - Par exemple, un caractère générique pour un motif qui peut ou non être présent. - Par exemple [a-z]* correspond à une chaîne vide ou à une chaîne de lettres minuscules.

. Correspond à n'importe quel caractère sauf la nouvelle ligne \n

  • Par exemple a. Correspond à une chaîne de deux caractères commençant par a et se terminant par n'importe quoi sauf \n

| Opérateur OR

  • Par exemple a|b signifie soit a ou b peuvent être appariés.
  • Par exemple red|white|orange correspond exactement à l'une des couleurs.

^ opérateur NOT

  • Par exemple [^0-9] le caractère ne peut pas contenir un nombre
  • Par exemple [^aA] le caractère ne peut pas être minuscule a ou majuscule A

\ Échappe le caractère spécial qui suit (remplace le comportement ci-dessus).

  • Par exemple \. , \\ , \( , \? , \$ , \^

Modèles d'ancrage :

^ La correspondance doit se produire au début de la chaîne

  • Par exemple ^a Le premier caractère doit être une lettre minuscule a
  • Par exemple ^[0-9] Le premier caractère doit être un chiffre.

$ La correspondance doit se produire à la fin de la chaîne

  • Par exemple a$ Le dernier caractère doit être une lettre minuscule a

Tableau de priorité :

Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |

Abréviations de caractères prédéfinis :

abr    same as       meaning
\d     [0-9]         Any single digit
\D     [^0-9]        Any single character that's not a digit
\w     [a-zA-Z0-9_]  Any word character
\W     [^a-zA-Z0-9_] Any non-word character
\s     [ \r\t\n\f]   Any space character
\S     [^ \r\t\n\f]  Any non-space character
\n     [\n]          New line

Exemple 1 : Exécuter en tant que macro

L'exemple de macro suivant examine la valeur de la cellule A1 pour voir si les 1 ou 2 premiers caractères sont des chiffres. Si c'est le cas, ils sont supprimés et le reste de la chaîne est affiché. Si ce n'est pas le cas, une boîte apparaît pour vous dire qu'aucune correspondance n'a été trouvée. Cellule A1 valeurs de 12abc retournera abc , la valeur de 1abc retournera abc , la valeur de abc123 renverra "Not Matched" parce que les chiffres ne sont pas au début de la chaîne.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1")

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

Exemple 2 : Exécuter comme une fonction dans la cellule

Cet exemple est le même que l'exemple 1, mais il est configuré pour être exécuté comme une fonction dans la cellule. Pour l'utiliser, changez le code en ceci :

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "^[0-9]{1,3}"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

Placez vos chaînes ("12abc") dans la cellule A1 . Entrez cette formule =simpleCellRegex(A1) en cellule B1 et le résultat sera "abc".

enter image description here


Exemple 3 : Gamme Loop Through

Cet exemple est identique à l'exemple 1, mais boucle sur une plage de cellules.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A5")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value
            strReplace = ""

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

Exemple 4 : Séparation de différents motifs

Cet exemple parcourt en boucle une plage ( A1 , A2 & A3 ) et recherche une chaîne commençant par trois chiffres, suivie d'un seul caractère alphabétique, puis de quatre chiffres. La sortie sépare les correspondances de motifs dans des cellules adjacentes en utilisant l'attribut () . $1 représente le premier modèle correspondant dans le premier ensemble de () .

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A3")

    For Each C In Myrange
        strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

        If strPattern <> "" Then
            strInput = C.Value
            strReplace = "$1"

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

Résultats :

enter image description here


Exemples de motifs supplémentaires

String   Regex Pattern                  Explanation
a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceeding alpha character
a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters

</i8>    \,\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit

31 votes

Vous ne devez pas oublier de Set regEx = Nothing . Vous obtiendrez des exceptions de type "Out of Memory", lorsque ce sous-programme est exécuté assez fréquemment.

1 votes

J'ai adapté l'exemple 4 avec SubMatches pour traiter des regex plus complexes, en fait je n'utilise pas replace lors du fractionnement, si cela intéresse quelqu'un : stackoverflow.com/questions/30218413/

14 votes

Ligne de reliure tardive : Set regEx = CreateObject("VBScript.RegExp")

220voto

Patrick Böker Points 2860

Pour utiliser les expressions régulières directement dans les formules Excel, l'UDF (user defined function) suivante peut être utile. Elle expose plus ou moins directement la fonctionnalité des expressions régulières comme une fonction Excel.

Comment cela fonctionne

Il faut 2 ou 3 paramètres.

  1. Un texte sur lequel utiliser l'expression régulière.
  2. Une expression régulière.
  3. Une chaîne de format spécifiant à quoi doit ressembler le résultat. Elle peut contenir $0 , $1 , $2 et ainsi de suite. $0 est le match entier, $1 et supérieur correspondent aux groupes de correspondance respectifs dans l'expression régulière. La valeur par défaut est $0 .

Quelques exemples

Extraction d'une adresse électronique :

=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")

Résultats dans : some@email.com

Extraction de plusieurs sous-chaînes :

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

Résultats dans : E-Mail: some@email.com, Name: Peter Gordon

Démonter une chaîne combinée dans une seule cellule en ses composants dans plusieurs cellules :

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)

Résultats dans : Peter Gordon some@email.com ...

Mode d'emploi

Pour utiliser cette UDF, il faut procéder comme suit (en se basant approximativement sur les éléments suivants cette page de Microsoft . On y trouve de bonnes informations complémentaires) :

  1. Dans Excel, dans un fichier compatible avec les macros (".xlsm"), appuyez sur la touche ALT+F11 pour ouvrir le Microsoft Visual Basic pour les applications Rédacteur en chef.

  2. Ajout d'une référence VBA à la bibliothèque d'expressions régulières (copiée sans vergogne à partir de Réponse des coureurs de Portland++. ) :

    1. Cliquez sur Outils -> Références (veuillez excuser la capture d'écran en allemand) Tools -> References
    2. Trouvez Expressions régulières Microsoft VBScript 5.5 dans la liste et cochez la case à côté.
    3. Cliquez sur OK .
  3. Cliquez sur Module d'insertion . Si vous donnez un nom différent à votre module, assurez-vous que le module ne fait pas partie de la liste des modules. no ont le même nom que l'UDF ci-dessous (par exemple, en nommant le module Regex et la fonction regex causes #NAME ! erreurs).

    Second icon in the icon row -> Module

  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. Sauvegardez et fermez le Microsoft Visual Basic pour les applications Fenêtre de l'éditeur.

82voto

SAm Points 1846

Élargir le champ d'action patszim 's réponse 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
    ! Excel VBA Form add references

  4. et en sélectionnant Expression régulière Microsoft VBScript 5.5
    ! Excel VBA add regex reference

  5. Insérez un nouveau module (le code doit résider dans le module, sinon cela ne fonctionne pas).
    ! Excel VBA insert code module

  6. Dans le module nouvellement inséré,
    ! Excel VBA insert code into module

  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 modèle de regex est placé dans l'une des cellules et référence absolue est utilisé sur celui-ci. ! Excel regex function in-cell usage La fonction sera liée au classeur dans lequel elle est créée.
    S'il est nécessaire de l'utiliser dans différents classeurs, stockez la fonction dans le dossier de l'utilisateur. Personnel.XLSB

30voto

Vikas Gautam Points 876

Voici ma tentative :

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

16voto

Alex Roseland Points 31

Il ne s'agit pas d'une réponse directe, mais d'une solution plus efficace à envisager. En effet, Google Sheets dispose de plusieurs fonctions intégrées, telles que Fonctions Regex Ceux-ci peuvent être très pratiques et permettent de contourner certaines des procédures techniques d'Excel. Il est évident que l'utilisation d'Excel sur votre PC présente des avantages, mais pour la grande majorité des utilisateurs, Google Sheets offre une expérience identique et peut présenter certains avantages en termes de portabilité et de partage des documents.

Ils offrent

REGEXEXTRACT : Extrait les sous-chaînes correspondantes en fonction d'une expression régulière.

REGEXREPLACE : Remplace une partie d'une chaîne de texte par une chaîne de texte différente en utilisant des expressions régulières.

SUBSTITUTE : Remplace le texte existant par un nouveau texte dans une chaîne.

REPLACE : Remplace une partie d'une chaîne de texte par une autre chaîne de texte.

Vous pouvez les taper directement dans une cellule comme ceci et vous obtiendrez ce que vous voulez.

=REGEXMATCH(A2, "[0-9]+")

Ils fonctionnent également très bien en combinaison avec d'autres fonctions telles que SI comme ça :

=IF(REGEXMATCH(E8,"MiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*")/1000,IF(REGEXMATCH(E8,"GiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*"),"")

enter image description here

Espérons que cela constitue une solution de contournement simple pour les utilisateurs qui se sentent brimés par le composant VBS d'Excel.

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X