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


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 en cellule pour renvoyer le motif correspondant ou la valeur remplacée dans la chaîne.
  • Sous-boucle à travers une colonne de données et extraire les correspondances aux cellules adjacentes.
  • Quelle configuration est nécessaire?
  • Quels sont les caractères spéciaux d'Excel pour les expressions régulières?

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 tapez des commandes pour des manipulations similaires.


412
2018-03-20 19:09


origine


Réponses:


Expressions régulières sont utilisés pour Pattern Matching.

Pour 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 qu'est-ce que je fais?)
  • Sélectionnez l'icône "Visual Basic" dans la section du ruban "Code"
  • Dans "Microsoft Visual Basic pour Applications" fenêtre sélectionnez "Outils" dans le menu supérieur.
  • Sélectionnez "Références"
  • Cochez la case "Microsoft VBScript Regular Expressions 5.5" à inclure dans votre classeur.
  • Cliquez sur OK"

Étape 2: Définissez votre motif

Définitions basiques:

-  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 l'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 à une seule lettre minuscule de l'alphabet.

() Regroupe des correspondances différentes à 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 consécutives a: aa
  • Par exemple. [a]{1,3} correspond à au moins un et jusqu'à trois lettres minuscules a, aa, aaa 

+  Faites correspondre au moins un, ou plus, du motif défini avant.

  • Par exemple. a+ correspondra à un consécutif a, aa, aaa, etc

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

  • Par exemple. Le motif peut ou peut ne pas être présent 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.  - Par exemple. Caractère générique pour le motif pouvant 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 newline \n

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

|  Opérateur OU

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

^  Pas opérateur

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

\  S'échappe au caractère spécial qui suit (remplace le comportement ci-dessus)

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

Modèles d'ancrage:

^La correspondance doit avoir lieu 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 nombre.

$  La correspondance doit avoir lieu à la fin de la chaîne

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

Table 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éfinies:

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 dans 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 vous indiquant qu'aucune correspondance n'est trouvée. Cellule A1 valeurs de 12abc reviendra abc, valeur de 1abc reviendra abc, valeur de abc123 retournera "Not Matched" car les chiffres n'étaient 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 en tant que fonction dans la cellule

Cet exemple est le même que l'exemple 1, mais il est configuré pour fonctionner en tant que fonction interne. 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: Plage de boucle

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éparer différents modèles

Cet exemple parcourt une plage (A1, A2 & A3) et recherche une chaîne commençant par trois chiffres suivie d'un seul caractère alpha, puis de quatre chiffres numériques. La sortie divise les correspondances de motif en cellules adjacentes en utilisant le (). $1 représente le premier motif apparié 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:

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

704
2018-03-20 19:09



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, etc. $0 est le match entier, $1 et plus 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: some@email.com, 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")

Résulte en: some@email.com

Extraire plusieurs sous-chaînes:

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

Résulte en: E-Mail: some@email.com, Name: Peter Gordon

Pour séparer 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ésulte en: Peter Gordon  some@email.com ...

Comment utiliser

Pour utiliser ce fichier UDF, procédez comme suit (en gros sur la base de cette page Microsoft. Ils ont de bonnes informations supplémentaires là-bas!):

  1. Dans Excel dans un fichier Macro activé ('.xlsm'), appuyez sur ALT+F11 ouvrir le Microsoft Visual Basic pour Applications Éditeur.
  2. Ajouter une référence VBA à la bibliothèque Regular Expressions (copiée sans vergogne à partir de Portland Runners ++ réponse):
    1. Cliquer sur Outils -> Les références (Veuillez excuser la capture d'écran allemande) Tools -> References
    2. Trouver Microsoft VBScript Regular Expressions 5.5 dans la liste et cochez la case correspondante.
    3. Cliquez sur D'accord.
  3. Cliquer sur Insérer un module. Si vous attribuez un nom différent à votre module, assurez-vous que le module ne pas portent le même nom que l'UDF ci-dessous (par exemple, nommer le module Regex et la fonction regex causes #PRÉNOM! les 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. Enregistrez et fermez le Microsoft Visual Basic pour Applications Fenêtre de l'éditeur


154
2018-01-27 17:47



Expansion sur patszimde répondre 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 Les références
    ![Excel VBA Form add references
  4. et en sélectionnant Microsoft VBScript Regular Expression 5.5
    ![Excel VBA add regex reference
  5. Insérez un nouveau module (le code doit résider dans le module sinon il 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 motif regex est placé dans l'une des cellules et référencement absolu est utilisé dessus. ![Excel regex function in-cell usage La fonction sera liée au classeur dans lequel elle a été créée.
    S'il est nécessaire de l'utiliser dans différents classeurs, stockez la fonction dans Personal.XLSB


31
2018-03-30 21:28



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

15
2017-08-17 23:03



Voici une regex_subst() fonction. 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 créer 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

2
2017-07-28 21:19



J'avais besoin de l'utiliser comme une fonction de cellule (comme SUM ou VLOOKUP) et a trouvé qu'il était facile de:

  1. Assurez-vous que vous êtes dans un fichier Excel Macro activé (enregistrer sous xlsm).
  2. Ouvrir les outils de développement Alt + F11
  3. Ajouter Microsoft VBScript Regular Expressions 5.5 comme dans d'autres réponses
  4. Créez la fonction suivante dans un classeur ou dans son propre module:

    Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
        Dim regex As New VBScript_RegExp_55.RegExp
        Dim strInput As String
    
        strInput = myRange.Value
    
        With regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
    
        REGPLACE = regex.Replace(strInput, outputPattern)
    
    End Function
    
  5. Ensuite, vous pouvez utiliser en cellule avec =REGPLACE(B1, "(\w) (\d+)", "$1$2") (ex: "A 243" à "A243")


1
2018-03-18 02:41