Reguliere expressies (Regex) gebruiken in Microsoft Excel, zowel in-cell als loops

Hoe kan ik reguliere expressies in Excel gebruiken en profiteren van de krachtige rasterachtige instellingen van Excel voor gegevensmanipulatie?

• In-celfunctie om een overeenkomend patroon of vervangen waarde in een tekenreeks te retourneren.
• Sub om door een kolom met gegevens te bladeren en overeenkomsten naar aangrenzende cellen te extraheren.
• Welke configuratie is nodig?
• Wat zijn de speciale tekens van Excel voor reguliere expressies?

Ik begrijp dat Regex voor veel situaties niet ideaal is (Reguliere expressies wel of niet gebruiken?) aangezien Excel dat wel kan gebruik Left, Mid, Right, Instrcommando’s voor soortgelijke manipulaties.

Antwoord 1, autoriteit 100%

Reguliere expressiesworden gebruikt voor Pattern Matching.

Volg deze stappen om in Excel te gebruiken:

Stap 1: VBA-verwijzing toevoegen aan “Microsoft VBScript Regular Expressions 5.5”

• Selecteer het tabblad “Ontwikkelaar” (Ik heb dit tabblad niet wat moet ik doen?)
• Selecteer het pictogram ‘Visual Basic’ in het lintgedeelte ‘Code’
• Selecteer in het venster “Microsoft Visual Basic for Applications” “Extra” in het bovenste menu.
• Selecteer “Referenties”
• Vink het vakje aan naast “Microsoft VBScript Regular Expressions 5.5” om op te nemen in uw werkmap.
• Klik op “OK”

Stap 2: bepaal je patroon

Basisdefinities:

-Bereik.

• Bijvoorbeeld a-zkomt overeen met kleine letters van a tot z
• Bijvoorbeeld 0-5komt overeen met elk getal van 0 tot 5

[]Zoek exact één van de objecten binnen deze haakjes.

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

()Groepeert verschillende overeenkomsten voor retourdoeleinden. Zie voorbeelden hieronder.

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

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

+Overeenkomen met ten minste één of meer van het patroon dat ervoor is gedefinieerd.

• Bijvoorbeeld a+komt overeen met opeenvolgende a’s a, aa, aaa, enzovoort

?Overeenkomen met nul of een van de patronen die ervoor zijn gedefinieerd.

• Bijvoorbeeld Patroon kan al dan niet aanwezig zijn, maar kan slechts één keer worden gekoppeld.
• Bijvoorbeeld [a-z]?komt overeen met een lege tekenreeks of een enkele kleine letter.

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

• Bijvoorbeeld Jokerteken voor patroon dat al dan niet aanwezig is.
• Bijvoorbeeld [a-z]*komt overeen met lege tekenreeks of reeks kleine letters.

.Komt overeen met elk teken behalve nieuwe regel \n

• Bijvoorbeeld a.Komt overeen met een tekenreeks van twee tekens die begint met a en eindigt met alles behalve \n

|OR-operator

• Bijvoorbeeld a|bbetekent dat aof bkan worden gevonden.
• Bijvoorbeeld red|white|orangekomt exact overeen met een van de kleuren.

^NOT-operator

• Bijvoorbeeld [^0-9]teken mag geen cijfer bevatten
• Bijvoorbeeld [^aA]teken mag geen kleine letter aof hoofdletter a

zijn

\Ontsnapt aan speciaal teken dat volgt (overschrijft bovenstaande gedrag)

• Bijvoorbeeld \., \\, \(, \?, \$, \^

Verankeringspatronen:

^Overeenkomst moet plaatsvinden aan het begin van de tekenreeks

• Bijvoorbeeld ^aHet eerste teken moet een kleine letter zijn a
• Bijvoorbeeld ^[0-9]Het eerste teken moet een getal zijn.

$Overeenkomst moet plaatsvinden aan het einde van de tekenreeks

• Bijvoorbeeld a$Laatste teken moet een kleine letter zijn a

Voorrangstabel:

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

Vooraf gedefinieerde letterafkortingen:

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

Voorbeeld 1: Uitvoeren als macro

De volgende voorbeeldmacro kijkt naar de waarde in cel A1om te zien of de eerste 1 of 2 tekens cijfers zijn. Als dat het geval is, worden ze verwijderd en wordt de rest van de tekenreeks weergegeven. Zo niet, dan verschijnt er een vak dat aangeeft dat er geen overeenkomst is gevonden. Cel A1waarden van 12abcgeven abcterug, waarde van 1abcgeven abcterug , zal de waarde van abc123“Niet overeenkomen” retourneren 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 functie in de cel

Dit voorbeeld is hetzelfde als voorbeeld 1, maar is ingesteld om te worden uitgevoerd als een functie in de cel. Om te gebruiken, verander de code in dit:

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 je strings (“12abc”) in cel A1. Voer deze formule =simpleCellRegex(A1)in cel B1in en het resultaat is “abc”.

Voorbeeld 3: Doorlusbereik

Dit voorbeeld is hetzelfde als voorbeeld 1, maar doorloopt een reeks 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: verschillende patronen splitsen

Dit voorbeeld doorloopt een bereik (A1, A2& A3) en zoekt naar een tekenreeks die begint met drie cijfers gevolgd door een één alfateken en vervolgens 4 numerieke cijfers. De uitvoer splitst de patroonovereenkomsten op in aangrenzende cellen met behulp van de (). $1staat voor het eerste patroon dat overeenkomt met de eerste set 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:

Extra 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 preceding 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

Antwoord 2, autoriteit 21%

Om rechtstreeks in Excel-formules gebruik te maken van reguliere expressies kan de volgende UDF (gebruiker gedefinieerde functie) van pas komen. Het stelt de functionaliteit van reguliere expressies min of meer direct bloot als een Excel-functie.

Hoe het werkt

Er zijn 2-3 parameters nodig.

1. Een tekst om de reguliere expressie op te gebruiken.
2. Een reguliere expressie.
3. Een opmaakreeks die aangeeft hoe het resultaat eruit moet zien. Het kan $0, $1, $2, enzovoort bevatten. $0is de volledige overeenkomst, $1en hoger komen overeen met de respectievelijke overeenkomstgroepen in de reguliere expressie. Standaard ingesteld op $0.

Enkele voorbeelden

Een e-mailadres extraheren:

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

Resulteert in: [email protected]

Verschillende substrings extraheren:

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

Resulteert in: E-Mail: [email protected], Name: Peter Gordon

Een gecombineerde tekenreeks in een enkele cel uit elkaar halen in zijn componenten in meerdere cellen:

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

Resulteert in: Peter Gordon[email protected]…

Hoe te gebruiken

Als u deze UDF wilt gebruiken, doet u het volgende (ruwweg gebaseerd op deze Microsoft-pagina. Ze hebben daar goede aanvullende informatie!):

1. Druk in Excel in een bestand met macro’s (‘.xlsm’) op ALT+F11om de editor Microsoft Visual Basic for Applicationste openen.
2. Voeg VBA-referentie toe aan de bibliotheek voor reguliere expressies (schaamteloos gekopieerd van Portland Runners++ antwoord):
   1. Klik op Extra-> Referenties(excuseer de Duitse screenshot)
      Referenties”>
   2. Zoek Microsoft VBScript Regular Expressions 5.5in de lijst en vink het selectievakje ernaast aan.
   3. Klik op OK.

3. Klik op Module invoegen. Als u uw module een andere naam geeft, zorg er dan voor dat de module nietdezelfde naam heeft als de onderstaande UDF (bijvoorbeeld door de module regexen de functie regexveroorzaakt #NAME!fouten).

   Module”>

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

   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. Sla en sluit de Microsoft Visual Basic for Applications Editor Venster.

Antwoord 3, autoriteit 8%

Uitbreiden op patszim‘s antwoordvoor wie haast heeft.

1. Open Excel-werkmap.
2. Alt+F11om het VBA/Macros-venster te openen.
3. Voeg een verwijzing naar regex toe onder Toolsen vervolgens Referenties
   
4. en selecteer Microsoft VBScript Regular Expression 5.5
   
5. Voeg een nieuwe module in (code moet in de module staan, anders werkt het niet).
   
6. In de nieuw ingevoegde module,
   

7. voeg de volgende code toe:

   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. Het regex-patroon wordt in een van de cellen geplaatst en er wordt absolute verwijzingop gebruikt.
   
   Functie wordt gekoppeld aan de werkmap waarin deze is gemaakt.
   Als het nodig is om het in verschillende werkmappen te gebruiken, slaat u de functie op in Personal.XLSB

Antwoord 4, autoriteit 3%

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

Antwoord 5

Dit is geen direct antwoord, maar kan een efficiënter alternatief zijn voor uw overweging. Dat is dat Google Spreadsheets verschillende ingebouwde Regex-functiesheeft die erg handig kunnen zijn en helpen bij het omzeilen enkele technische procedures in Excel. Het is duidelijk dat er enkele voordelen zijn aan het gebruik van Excel op uw pc, maar voor de grote meerderheid van de gebruikers biedt Google Spreadsheets een identieke ervaring en kan het enkele voordelen bieden op het gebied van overdraagbaarheid en het delen van documenten.

Ze bieden

REGEXEXTRACT: extraheert overeenkomende subtekenreeksen volgens een reguliere expressie.

REGEXREPLACE: Vervangt een deel van een tekenreeks door een andere tekenreeks met behulp van reguliere expressies.

VERVANGING: Vervangt bestaande tekst door nieuwe tekst in een string.

REPLACE: Vervangt een deel van een tekenreeks door een andere tekenreeks.

Je kunt deze rechtstreeks in een cel typen, en je zult produceren wat je maar wilt

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

Ze werken ook heel goed in combinatie met andere functies zoals IF-statements zoals:

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

Hopelijk biedt dit een eenvoudige oplossing voor gebruikers die zich beschimpt voelen door de VBS-component van Excel.

Antwoord 6

Ik moest dit als een celfunctie gebruiken (zoals SUMof VLOOKUP) en ontdekte dat het gemakkelijk was om:

1. Zorg ervoor dat u zich in een voor macro’s geschikt Excel-bestand bevindt (opslaan als xlsm).
2. Ontwikkelaarstools Alt+ F11

openen

3. Voeg Microsoft VBScript Regular Expressions 5.5 toezoals in andere antwoorden

4. Maak de volgende functie in de werkmap of in zijn eigen 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. Dan kun je in cel gebruiken met =REGPLACE(B1, "(\w) (\d+)", "$1$2")(bijvoorbeeld: “A 243″ tot ” A243″)

Antwoord 7

Hier is een functie regex_subst(). Voorbeelden:

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

Hier is de vereenvoudigde code (in ieder geval eenvoudiger voor mij). Ik kon er niet achter komen hoe ik een geschikt uitvoerpatroon kon bouwen met behulp van het bovenstaande om te werken zoals mijn voorbeelden:

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

Antwoord 8

Ik wil geen referentiebibliotheek inschakelen omdat ik mijn scripts draagbaar wil maken.De regel Dim foo As New VBScript_RegExp_55.RegExpveroorzaakte User Defined Type Not Defined-fouten, maar ik heb een oplossing gevonden die voor mij werkte.

Reacties van RE bijwerken met @chrisneilsen :

Ik had de indruk dat het inschakelen van een referentiebibliotheek gekoppeld was aan de instellingen van de lokale computer, maar het is in feite rechtstreeks gekoppeld aan de werkmap.U kunt dus een referentiebibliotheek inschakelen, delen een werkmap met macro’s en de eindgebruiker zou de bibliotheek niet ook hoeven in te schakelen. Waarschuwing: Het voordeel van Late Binding is dat de ontwikkelaar zich geen zorgen hoeft te maken dat de verkeerde versie van een objectbibliotheek op de computer van de gebruiker wordt geïnstalleerd.Dit zou waarschijnlijk geen probleem zijn met de VBScript_RegExp_55.RegExpbibliotheek, maar ik ben niet verkocht dat het “prestatie” voordeel op dit moment de moeite waard is voor mij, aangezien we het hebben over onmerkbare milliseconden in mijn code. Ik vond dat dit een update verdiende om anderen te helpen het te begrijpen. Als je de referentiebibliotheek inschakelt, kun je “early bind” gebruiken, maar als je dat niet doet, voor zover ik weet, werkt de code prima, maar je moet “late bind” gebruikenen verliezen op sommige prestatie-/foutopsporingsfuncties.

Bron: https://peltiertech.com/Excel/EarlyLateBinding.html

Wat u wilt doen, is een voorbeeldreeks in cel A1plaatsen en vervolgens uw strPatterntesten. Als dat werkt, pas dan rngaan zoals gewenst.

Public Sub RegExSearch()
'https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
'https://wellsr.com/vba/2018/excel/vba-regex-regular-expressions-guide/
'https://www.vitoshacademy.com/vba-regex-in-excel/
    Dim regexp As Object
    'Dim regex As New VBScript_RegExp_55.regexp 'Caused "User Defined Type Not Defined" Error
    Dim rng As Range, rcell As Range
    Dim strInput As String, strPattern As String
    Set regexp = CreateObject("vbscript.regexp")
    Set rng = ActiveSheet.Range("A1:A1")
    strPattern = "([a-z]{2})([0-9]{8})"
    'Search for 2 Letters then 8 Digits Eg: XY12345678 = Matched
    With regexp
        .Global = False
        .MultiLine = False
        .ignoreCase = True
        .Pattern = strPattern
    End With
    For Each rcell In rng.Cells
        If strPattern <> "" Then
            strInput = rcell.Value
            If regexp.test(strInput) Then
                MsgBox rcell & " Matched in Cell " & rcell.Address
            Else
                MsgBox "No Matches!"
            End If
        End If
    Next
End Sub