Excel-regex-tutorial: patronen herkennen met reguliere expressies

RegEx gebruiken in Excel

Hoewel Excel niet standaard RegEx ondersteunt, kun je het inschakelen door je eigen aangepaste functie te schrijven met een VBA-script (voor Windows) of door add-ins in te schakelen (voor Mac). We lopen door hoe je een regex-functie inschakelt om tekst uit een kolom in Excel te extraheren.

Voor Windows

Druk om te beginnen op Alt+F11 om de Visual Basic Editor te openen. Je kunt er ook naartoe navigeren via Extra > Macro > Visual Basic Editor. Klik daarna op Invoegen > Module.

Plak de volgende functie in de module:

Public Function CustomRegExpExtract(inputText As String, regexPattern As String, Optional instanceNumber As Integer = 0, Optional matchCase As Boolean = True) As Variant
    Dim textMatches() As String
    Dim matchesIndex As Integer
    Dim regex As Object
    Dim matches As Object
    
    On Error GoTo ErrHandler
    
    CustomRegExpExtract = ""
    
    ' Create a regular expression object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = regexPattern
    regex.Global = True
    regex.MultiLine = True
    
    ' Set case sensitivity
    If matchCase Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
    
    ' Execute the regex pattern on the input text
    Set matches = regex.Execute(inputText)
    
    If matches.Count > 0 Then
        If instanceNumber = 0 Then
            ' If instanceNumber is 0, return all matches in an array
            ReDim textMatches(matches.Count - 1, 0)
            For matchesIndex = 0 To matches.Count - 1
                textMatches(matchesIndex, 0) = matches.Item(matchesIndex)
            Next matchesIndex
            CustomRegExpExtract = textMatches
        Else
            ' If instanceNumber is specified, return the match at that instance
            CustomRegExpExtract = matches.Item(instanceNumber - 1)
        End If
    End If
    
    Exit Function

ErrHandler:
    ' Handle errors by returning an error value
    CustomRegExpExtract = CVErr(xlErrValue)
End Function

' Modified from an original script by Patrick Matthews

Sla de functie op en ga terug naar het werkblad. Sla de werkmap nu op als een macro-compatibele werkmap met de extensie .xlsm.

De functie gebruiken in een Excel-werkblad

Door macro’s in je Excel-werkmap in te schakelen, kun je de functie CustomRegExpExtract nu net als een gewone functie in Excel gebruiken om complexe stringpatronen te parsen, waarbij:

• inputText de originele string is die je wilt parsen;

• regexPattern het reguliere-expressiepatroon is dat wordt gebruikt om de inputText te parsen;

• instanceNumber standaard alle instanties van het patroon retourneert, maar je kunt een geheel getal invoeren om het aantal te retourneren instanties te specificeren; en

• matchCase aangeeft of je hoofdlettergebruik meeweegt tijdens het matchen (TRUE of weggelaten) of juist negeert (FALSE).

Stel dat we geïnteresseerd zijn in het extraheren van telefoonnummers uit een veld. We kunnen het regex-patroon in een willekeurige cel typen (in het onderstaande geval staat het in cel A2). In cel A6 staat je vrije-tekstvoorbeeld, "Sophia, 1111111111". In cel B6 typen we =CustomRegExpExtract(A6,$A$2). De functie extraheert het telefoonnummer door de string te matchen met het regex-patroon in cel A2 en retourneert "1111111111", zoals verwacht.

Voor Mac OS

Helaas herkent Mac OS de bibliotheek MS VBScript Regular Expressions 5.5 niet. Om complexe patroonherkenning in Excel op een Mac mogelijk te maken, zijn er twee opties: gebruik geavanceerde filters of installeer pakketten.

Geavanceerde filters gebruiken

Geavanceerde filters in Excel helpen bij het uitvoeren van complexe gegevensfilterbewerkingen op basis van meerdere criteria. Deze functie is vooral handig bij grote datasets. Zo gebruik je geavanceerde filters in Excel:

• Je gegevens voorbereiden: Zorg dat je gegevens in een tabelachtige opmaak staan met duidelijke koppen.

• Een criteriabereik maken: Maak een apart gebied in je werkblad om de criteria voor het filteren te definiëren. Dit gebied moet dezelfde koppen hebben als de kolommen die je wilt filteren. Specificeer onder deze koppen de criteria voor het filteren. Je kunt meerdere criteria onder dezelfde kop invoeren voor een "OF"-logica, of in afzonderlijke rijen voor een "EN"-logica.

• Selecteer de gegevens: Klik op een cel binnen je dataset, of selecteer het volledige bereik dat je wilt filteren.

• Stel het filter in: Klik op het tabblad Gegevens in het lint van Excel en ga naar de optie Geavanceerd filter. Klik op Geavanceerd in de groep Sorteren & filteren. Kies in het dialoogvenster Geavanceerd filter of je de lijst ter plekke wilt filteren of de resultaten naar een andere locatie wilt kopiëren. Geef het Lijstreferentiebereik op (je gegevens). Geef het Criteriabereik op (waar je de filtervoorwaarden hebt ingesteld). Klik op OK om het filter toe te passen.

Stel, je hebt een dataset met de kolommen Name, Age, City. Je wilt filteren op mensen ouder dan 30 in New York of iedereen in Los Angeles. Je criteriabereik kan er als volgt uitzien:

Add-ins installeren

• Kutools for Excel: Kutools is een uitgebreide add-in met veel functies, waaronder de mogelijkheid om data te zoeken en te vervangen met regex. Er wordt een nieuw tabblad aan het Excel-lint toegevoegd met een breed scala aan tools, inclusief regex-functies.

• RegEx Find/Replace: Dit is een add-in voor Excel waarmee je tekst kunt zoeken en vervangen met reguliere expressies. Het integreert in Excel en biedt geavanceerdere zoek-/vervangfunctionaliteit dan de ingebouwde optie.

• PowerGREP: Hoewel dit geen directe Excel-add-in is, kan PowerGREP met Excel-bestanden werken. Het is een krachtige grep-tool voor Windows waarmee je complexe regex-bewerkingen op tekst- en binaire bestanden kunt uitvoeren. Het is meer een standalone-applicatie, maar kan in combinatie met Excel worden gebruikt voor geavanceerde regex-verwerking. Het is echter niet goedkoop. Een licentie voor één gebruiker kost $159 en de prijs loopt op tot $5.300 voor een licentie voor 100 gebruikers.

Sommige add-ins zijn rechtstreeks in Excel te vinden via het tabblad Invoegen > Invoegtoepassingen ophalen.

Andere moeten van een externe bron worden gedownload en volgens hun installatie-instructies worden geïnstalleerd.

Enkele aandachtspunten:

• Compatibiliteit: Zorg dat de add-in compatibel is met jouw Excel-versie en besturingssysteem.
• Beveiliging: Download en installeer add-ins alleen van betrouwbare bronnen.
• Functionele behoeften: Bedenk welke regex-taken je wilt uitvoeren en kies een add-in die daar het beste bij past.

Conclusie

In de meeste gevallen helpen ingebouwde functies zoals FIND, SEARCH en REPLACE je met 90% van je tekstbewerkingsbehoeften in Excel. Maar voor de overige 10%, in gevallen met complexere stringpatronen, kan regex enorm helpen. Wil je meer leren over het toepassen van reguliere expressies in populaire programmeertalen? Bekijk dan onze cursussen Regular Expressions in Python en Intermediate Regular Expressions in R.

Wil je snel wegwijs worden in alles rond Excel? Bekijk dan onze Excel Fundamentals-skilltrack, die je meeneemt door de essentiële vaardigheden om vaardig met de tool te worden.