Hoe Reguliere Expressies (Regex) te gebruiken in Microsoft Excel zowel in-cel en lussen

Reguliere uitdrukkingen worden gebruikt voor Pattern Matching.

Volg deze stappen om ze in Excel te gebruiken:

Stap 1: Voeg VBA referentie toe aan "Microsoft VBScript Reguliere Expressies 5.5"

• Selecteer "Developer" tab (I don't have this tab what do I do?)
• Selecteer "Visual Basic" pictogram van 'Code' lint sectie
• In "Microsoft Visual Basic for Applications" venster selecteert u "Tools" uit het bovenste menu.
• Selecteer "Referenties"
• Vink het vakje naast "Microsoft VBScript Regular Expressions 5.5" aan om op te nemen in uw werkmap.
• Klik op "OK"

Stap 2: Definieer uw patroon

Basis definities:

- Bereik.

• B.v. a-z komt overeen met kleine letters van a tot z
• Bv. 0-5 komt overeen met een cijfer van 0 tot 5

[] Komt overeen met precies één van de objecten binnen deze haakjes.

• Bv. [a] komt overeen met de letter a
• Bv. [abc] komt overeen met een enkele letter die a, b of c kan zijn
• Bv. [a-z] komt overeen met elke kleine letter van het alfabet.

() Groepeert verschillende overeenkomsten voor teruggave doeleinden. Zie onderstaande voorbeelden.

{} Vermenigvuldiger voor herhaalde kopieën van het patroon dat ervoor gedefinieerd is.

• Bv. [a]{2} komt overeen met twee opeenvolgende kleine letters a: aa
• Bv. [a]{1,3} komt overeen met ten minste één en maximaal drie kleine letters a, aa, aaa

+ Komt overeen met ten minste één, of meer, van het daarvoor gedefinieerde patroon.

• Bv. a+ komt overeen met opeenvolgende a's a, aa, aaa, enzovoort

? Kom overeen met nul of één van het patroon ervoor.

• Bv. Patroon kan wel of niet aanwezig zijn, maar kan maar één keer worden gematched.
• B.v. [a-z]? komt overeen met een lege tekenreeks of een enkele kleine letter.

* Komt overeen met nul of meer van het patroon dat ervoor gedefinieerd is.

• Bv. Wildcard voor patroon dat wel of niet aanwezig kan zijn.
• B.v. [a-z]* komt overeen met een lege tekenreeks of een tekenreeks met kleine letters.

. Komt overeen met elk teken behalve de nieuwe regel `.

• Bv. a. Zoekt een tekenreeks van twee tekens beginnend met a en eindigend met iets behalve n.

| OR operator

• Bv. a|b betekent dat òf a òf b gematched kan worden.
• Bv. rood|wit|oranje komt overeen met precies een van de kleuren.

^ NIET operator

• Bv. [^0-9] teken kan geen getal bevatten
• Bv. [^aA] teken kan geen kleine letter a of hoofdletter A zijn

`` Ontsnapt speciaal teken dat volgt (overschrijft bovenstaand gedrag)

• Bijv. .`, ```, `(`),?, ``$, `^

Anchoring Patterns:

^ Overeenstemming moet aan het begin van een string voorkomen

• Bijv. ^a Eerste teken moet kleine letter a zijn
• Bijv. ^[0-9] Eerste teken moet een cijfer zijn.

$ De overeenkomst moet aan het einde van de tekenreeks voorkomen

• Bijv. a$ Laatste teken moet een kleine letter a zijn

Voorrangstabel:

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

Predefined Character Abbreviations:

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

Exemplaar 1: Uitvoeren als macro

De volgende voorbeeld macro kijkt naar de waarde in cel A1 om te zien of de eerste 1 of 2 karakters cijfers zijn. Zo ja, dan worden ze verwijderd en wordt de rest van de string weergegeven. Zo niet, dan verschijnt er een kader met de mededeling dat er geen overeenkomst is gevonden. Cel A1 waarde van 12abc zal abc opleveren, waarde van 1abc zal abc opleveren, waarde van abc123 zal "Not Matched" opleveren omdat de cijfers niet aan het begin van de tekenreeks stonden.

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

Voorbeeld 2: Uitvoeren als een in-cell functie

Dit voorbeeld is hetzelfde als voorbeeld 1 maar is ingesteld om als een in-cell functie te werken. Om het te gebruiken, wijzigt u de code als volgt:

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

Plaats uw tekenreeks ("12abc") in cel A1. Voer deze formule =simpleCellRegex(A1) in cel B1 in en het resultaat zal zijn "abc".

Exemplaar 3: Loop Through Range

Dit voorbeeld is hetzelfde als voorbeeld 1 maar maakt een lus door een bereik van cellen.

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

Voorbeeld 4: Het uit elkaar halen van verschillende patronen

Dit voorbeeld loopt door een bereik (A1, A2 & A3) en zoekt naar een tekenreeks die begint met drie cijfers, gevolgd door een enkel alfa teken en dan 4 numerieke cijfers. De uitvoer splitst de patroon overeenkomsten op in aangrenzende cellen door gebruik te maken van de (). $1 vertegenwoordigt het eerste patroon dat overeenkomt binnen de eerste reeks van ().

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

Resultaten:

Aanvullende patroonvoorbeelden

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

Om reguliere expressies direct in Excel formules te gebruiken kan de volgende UDF (user defined function) uitkomst bieden. Deze stelt de reguliere expressie-functionaliteit min of meer direct beschikbaar als een Excel-functie.

Hoe het werkt

Het neemt 2-3 parameters.

1. Een tekst om de reguliere expressie op te gebruiken.
2. Een reguliere uitdrukking.
3. Een format string die aangeeft hoe het resultaat er uit moet zien. Het kan $0, $1, $2, enzovoort bevatten. $0 is de volledige match, $1 en hoger komen overeen met de respectievelijke match groepen in de reguliere expressie. Standaard wordt $0 gebruikt.

Enkele voorbeelden

Een e-mail adres uitpakken:

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

Resulteert in: some@email.com

Het extraheren van verschillende substrings:

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

Resulteert in: E-Mail: some@email.com, Naam: Peter Gordon`

Om een gecombineerde string in een enkele cel uit elkaar te halen in zijn componenten in meerdere cellen:

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

Resulteert in: Peter Gordon some@email.com ...

Hoe te gebruiken

Om deze UDF te gebruiken doe je het volgende (ruwweg gebaseerd op deze Microsoft pagina. Ze hebben wat goede aanvullende info daar!):

1. Druk in Excel in een Macro enabled file ('.xlsm') op ALT+F11 om de Microsoft Visual Basic for Applications Editor te openen.
2. Voeg VBA verwijzing naar de Reguliere Expressies bibliotheek toe (schaamteloos gekopieerd van Portland Runners++ antwoord):
   1. Klik op Tools -> References (excuseer de duitse schermafdruk)
   2. Zoek Microsoft VBScript Regular Expressions 5.5 in de lijst en vink het vakje ernaast aan.
   3. Klik op OK.
3. Klik op Insert Module. Als u uw module een andere naam geeft, zorg er dan voor dat de Module niet dezelfde naam heeft als de UDF eronder (bijv. het noemen van de Module Regex en de functie regex veroorzaakt #NAME! fouten).

4. Voeg in het grote tekstvenster in het midden het volgende in:

    Functie regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") Als 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 als Object, replaceMatches als Object, replaceMatch als Object
        Dim replaceNumber Als Integer

        Met inputRegexObj
            .Global = True
            .MultiLine = True
            .NegeerHoofdletter = Onwaar
            .Patroon = matchPatroon
        Eindig met
        Met outputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)"
        Eindig met
        Met outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        Eindig met

        Set inputMatches = inputRegexObj.Execute(strInput)
        Als inputMatches.Count = 0 Dan
            regex = False
        Anders
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            Voor elke replaceMatch in replaceMatches
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$" & replaceNumber

                Als replaceNumber = 0 Dan
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Anders
                    Als replaceNumber > inputMatches(0).SubMatches.Count Dan
                        'regex = "Een tot hoge $-tag gevonden. Grootst toegestane is $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Functie afsluiten
                    Else
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                Einde Als
            Volgende
            regex = outputPattern
        End If
    Einde Functie

5. Sla op en sluit het Microsoft Visual Basic for Applications Editor venster.

Hier is mijn poging:

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