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

Les [expressions régulières] (http://en.wikipedia.org/wiki/Regular_expressions) sont utilisées pour la correspondance de motifs.

Pour les utiliser dans Excel, suivez les étapes suivantes :

Etape 1 : Ajouter la référence VBA à "Microsoft VBScript Regular Expressions 5.5&quot ;

• Sélectionnez l'onglet "Developer&quot ; (Je n'ai pas cet onglet que dois-je faire ?)
• Sélectionnez l'icône "Visual Basic&quot ; dans la section du ruban 'Code&#39 ;.
• Dans la fenêtre "Microsoft Visual Basic for Applications", sélectionnez "Tools" dans le menu supérieur.
• Sélectionnez "Références" ;
• Cochez la case située à côté 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 aux lettres minuscules de a à z.
• Par exemple, "0-5" correspond à un nombre de 0 à 5.

[] Correspond exactement à l'un des objets contenus dans ces crochets.

• Par exemple, [a] correspond à la lettre a.
• Par exemple, "[abc]`" correspond à une seule lettre qui peut être a, b ou c.
• Ex. : " [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 plus, du motif défini avant lui.

• Par exemple, a+ correspondra aux a's consécutifs a, aa, aaa, et ainsi de suite.

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

• Par exemple, le motif peut être présent ou non, mais ne peut être trouvé 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 le saut de 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 que l'on peut faire correspondre soit a soit b.
• Par exemple, rouge|blanc|orange correspond à une seule des couleurs.

Opérateur ^ NOT

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

Le caractère spécial qui suit est échappé (remplace le comportement ci-dessus).

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

Modèles d'ancrage:

^ La correspondance doit se produire au début de la chaîne de caractères.

• Ex. : ^a Le premier caractère doit être la lettre minuscule a.
• Ex. : ^[0-9] Le premier caractère doit être un chiffre.

$ La correspondance doit intervenir à la fin de la chaîne de caractères.

• Ex. : a$ Le dernier caractère doit être la lettre minuscule a.

Tableau de préséance:

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 indiquer qu'aucune correspondance n'a été trouvée. Dans la cellule A1, la valeur 12abc renvoie abc, la valeur 1abc renvoie abc, la valeur abc123 renvoie "Not Matched&quot ; 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

        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 in-cell. Pour l'utiliser, modifiez le code comme suit :

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 votre chaîne de caractères ("12abc&quot ;) dans la cellule A1. Entrez cette formule =simpleCellRegex(A1) dans la cellule B1 et le résultat sera "abc&quot ;.

! [entrer la description de l'image ici] [1]

Exemple 3 : Loop Through Range

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

            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 &amp ; A3) et recherche une chaîne de caractères commençant par trois chiffres, suivie d'un seul caractère alphabétique, puis de quatre chiffres. La sortie divise les correspondances de motifs en cellules adjacentes à l'aide du signe "()".$1représente le premier motif trouvé dans le premier ensemble de()`.

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput 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

            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 :

![entrez la description de l'image ici][2]

Exemples supplémentaires de motifs

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

    \            Exact non-word character except any single alpha followed by any single digit

[1] : http://i.stack.imgur.com/q3RRC.png [2] : http://i.stack.imgur.com/9eCZ5.png

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-t-il ?

Elle prend 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 l'apparence du résultat. Elle peut contenir $0, $1, $2, et ainsi de suite. $0 est la correspondance entière, $1 et plus 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 : some@email.com

Extraction de plusieurs sous-chaînes :

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

Résultats : Mail électronique : some@email.com, Nom : Peter Gordon

Pour décomposer 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 : Peter Gordon some@email.com ...

Comment utiliser

Pour utiliser cette UDF, procédez comme suit (en vous basant sur [cette page Microsoft][1]. Elle contient des informations complémentaires intéressantes) :

1. Dans Excel, dans un fichier compatible avec les macros (&#39 ;.xlsm&#39 ;) appuyez sur ALT+F11 pour ouvrir l'éditeur Microsoft Visual Basic for Applications.
2. Ajoutez une référence VBA à la bibliothèque Regular Expressions (copiée sans vergogne de [Portland Runners++ answer][2]) :
   1. Cliquez sur Outils -> Références (veuillez excuser la capture d'écran en allemand) ![Outils -> Références][3]
   2. Trouvez Microsoft VBScript Regular Expressions 5.5 dans la liste et cochez la case à côté.
   3. Cliquez sur OK.
3. Cliquez sur Insérer le 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!).

![Deuxième icône dans la rangée d'icônes -> Module][4] 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&quot ;) 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

        Avec inputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
        Avec outputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)&quot ;
        End With
        Avec outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        End With

        Set inputMatches = inputRegexObj.Execute(strInput)
        Si inputMatches.Count = 0 Alors
            regex = False
        Else
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            For Each replaceMatch In replaceMatches
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$&quot ; &amp ; replaceNumber

                Si nombre de remplacement = 0, alors
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Sinon,
                    Si replaceNumber > inputMatches(0).SubMatches.Count Alors
                        'regex = "Une à haute balise $ trouvée. Le plus grand autorisé est $&quot ; &amp ; inputMatches(0).SubMatches.Count &amp ; &quot ;.&quot ;
                        regex = CVErr(xlErrValue)
                        Quitter la fonction
                    Else
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                End If
            Suivant
            regex = modèle de sortie
        End If
    Fin de la fonction

5. Enregistrez et fermez la fenêtre de l'éditeur Microsoft Visual Basic for Applications.

[1] : https://support.office.com/en-in/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f "Cette page Microsoft&quot ; [2] : /questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#answer-22542835 [3] : http://i.stack.imgur.com/etyH4.png [4] : http://i.stack.imgur.com/nlTpn.png

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