VBA InStr-functie: Zo zoek je naar tekst in Excel VBA

Als je meer dan een paar VBA-macro’s hebt geschreven, ben je vast dit moment tegengekomen waarop je wilt controleren of bepaalde tekst in een cel voorkomt. In VBA is InStr zo’n functie die je overal gebruikt. Hij vertelt je waar een stuk tekst binnen een andere tekst verschijnt. Je kunt hem gebruiken om te checken of tekst in een cel staat, waarden in tekst te parsen of voorwaardelijke logica aan te sturen in macro’s die van tekstpatronen afhangen.

In deze gids neem ik je mee door de InStr-syntax, een paar voorbeelden, hoe vergelijkingsmodi werken (dit deel zorgt vaker voor verwarring dan nodig is) en enkele patronen die je écht gaat hergebruiken.

Ik raad je aan te beginnen met onze Excel Fundamentals skill track om een goede basis te leggen voordat je doorgaat met VBA, of volg onze cursus Advanced Excel Functions als je al wat ervaring hebt, om meer te leren over hoe functies de efficiëntie in data-analyse verbeteren.

Wat is de VBA-functie InStr?

InStr is een VBA-functie die de positie teruggeeft van het eerste voorkomen van een string binnen een andere.

De resultaten werken als volgt:

• Als er een match is, retourneert hij een getal (de positie waar de match start)
• Als er niets gevonden wordt, retourneert hij 0

Als bijvoorbeeld “cat” op positie 5 in een string staat, krijg je 5. Als hij er niet is, krijg je 0.

VBA InStr-syntax

Dit is de volledige InStr-syntax:

InStr([start], string1, string2, [compare])

Waarbij:

• start (optioneel): Hiermee geef je aan waar VBA met zoeken moet beginnen. Als je dit weglaat, start VBA op positie 1.

• string1 (verplicht): De volledige tekst waarin je zoekt.

• string2 (verplicht): De tekst die je probeert te vinden.

• compare (optioneel): Bepaalt hoe de vergelijking wordt gedaan (wel of niet hoofdlettergevoelig). Als je dit weglaat, gebruikt VBA de standaardvergelijking, meestal hoofdlettergevoelig.

Let op dat je de argumenten start en compare kunt negeren (InStr(string1, string2)) voor een eenvoudigere variant. Maar als je het argument compare gebruikt, moet je ook het argument start opgeven.

Hoe VBA InStr werkt

Zie InStr als het lezen van een zin van links naar rechts, één teken per keer.

De functie begint met string1, scant die en zoekt naar de eerste plek waar string2 overeenkomt. Zodra hij die match vindt, stopt hij en geeft de positie terug waar die begon.

De “match”-positie is belangrijk, want VBA telt vanaf 1, niet vanaf 0. Dus het eerste teken in een string is positie 1, het tweede is 2, enzovoort. 

VBA InStr-returnwaarden uitgelegd

Het interpreteren van het resultaat van een InStr-functie is eenvoudig. Je krijgt een van de volgende:

• Positief getal: als er een match is, en het getal vertelt je waar die begint.

• 0: als er nergens in de string een match is

Als je invoerstrings echter Null zijn, retourneert de functie Null. Hoewel je dit bij basis-macro’s kunt negeren, is het goed om te weten dat dit verschil ongemerkt een voorwaarde kan breken.

Basisvoorbeelden van VBA InStr

Laat me nu een paar voorbeelden laten zien van hoe je de VBA-functie InStr gebruikt. 

Zoek een woord in een zin

Om te controleren of het woord “before” voorkomt in de notitie “Deliver before noon”.

Sub Before()

    Dim result As Integer
    result = InStr("Deliver before noon", "before")
    
    MsgBox result

End Sub

Het resultaat voor de bovenstaande VBA is 9 omdat “before” bij het 9e teken begint.

Controleren of een teken bestaat

Om te controleren of het teken “0” voorkomt in een gegeven string, gebruik je de VBA:

Sub ChExist()

    Dim result As Integer
    result = InStr(“A1023”, “0”)
    
    MsgBox result

End Sub

In dit geval betekent elk resultaat groter dan 0 dat het teken is gevonden.

Zoek vanaf het begin van een string

Om vanaf het begin van een string te zoeken hoef je de startpositie niet op te geven, dus begint hij op 1. Dat is elke keer de standaard, tenzij je anders aangeeft.

Sub ChExist()

    Dim result As Integer
    result = InStr("Report_2026_Final", "2026")
    
    MsgBox result

End Sub

VBA InStr gebruiken in If-statements

Als je met een echte spreadsheet werkt, zoek je niet alleen een getal; je probeert een beslissing te nemen. De meest gebruikelijke manier om InStr te gebruiken is binnen een If-statement om data te filteren.

In deze tutorial gebruiken we een voorbeeld-dataset, Orders, met de kolommen: ID, CustomerName, Email, City en OrderNotes.

If InStr(...) > 0 Then

Omdat InStr 0 retourneert wanneer er niets gevonden wordt, controleren we of het resultaat bestaat wanneer het een getal groter dan 0 teruggeeft.

In het onderstaande voorbeeld kijkt het naar de kolom OrderNotes (kolom 5). Als het het woord “Fragile” vindt, schrijft het “Handle Carefully” in kolom 6.

Sub ChExist()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        ' Check if the order notes mention "Fragile"
        If InStr(Cells(i, 5).Value, "Fragile") > 0 Then
            Cells(i, 6).Value = "Handle Carefully"
        End If
        
    Next i

End Sub

Voorwaardelijke checks voor trefwoorden en tekstfiltering

Je kunt de functie InStr ook gebruiken om te zoeken naar trefwoorden in strings, vooral als de uitdrukking licht varieert. In de code hieronder wordt bijvoorbeeld gecontroleerd of de notitie “Call” bevat (zoals “Call on arrival” of “Call before delivery”). Daarna wordt dit gemarkeerd en de tag “Cal Customer” toegevoegd in kolom 6.

Sub ChExist()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        ' Check if the order notes mention “Call”
        If InStr(Cells(i, 5).Value, “Call”) > 0 Then
            Cells(i, 6).Value = “Call Customer”
        End If
        
    Next i

End Sub

VBA InStr: hoofdlettergevoeligheid en vergelijkingsmodi

Standaard is InStr hoofdlettergevoelig (genoemd vbBinaryCompare). Stel dat je zoekt naar “chicago” maar de data zegt “Chicago”: InStr retourneert dan 0.

Om dit op te lossen, gebruik je het argument compare waarbij:

• vbBinaryCompare (Standaard): hoofdlettergevoelig (“A” is niet hetzelfde als “a”.)

• vbTextCompare: niet-hoofdlettergevoelig “A” is hetzelfde als “a”.

In het onderstaande voorbeeld retourneert InStr 0 omdat er gezocht wordt naar “fragile” (kleine letters) binnen “Fragile items”.

Sub CheckFragile()

    Dim result As Integer
    
    result = InStr(1, "Fragile items", "fragile", vbBinaryCompare)
    
    MsgBox result

End Sub

Met vbTextCompare krijg je echter 1 bij dezelfde zoekactie, omdat de lettergrootte wordt genegeerd.

Sub CheckFragile()

    Dim result As Integer
    
    result = InStr(1, "Fragile items", "fragile", vbTextCompare)
    
    MsgBox result

End Sub

VBA InStr met een startpositie

Soms wil je niet aan het begin van de string starten met zoeken. Het argument start geeft aan waar VBA moet beginnen met zoeken.

In het onderstaande voorbeeld is het argument start ingesteld op 10, dus VBA begint bij het 10e teken in plaats van het eerste. Het vindt vervolgens later in de string "delivery".

Sub CheckUrgent()

    Dim result As Integer
    
    result = InStr(10, “Deliver before noon - urgent delivery”, “delivery”)
    
    MsgBox result

End Sub

Het resultaat van het bovenstaande voorbeeld is 31, de positie waar "delivery" begint.

Deze methode is handig als je de tweede keer dat iets voorkomt wilt vinden of een bekende prefix wilt negeren bij herhalende patronen.

VBA InStr vs. InStrRev

Zoals je al hebt geleerd, zoekt InStr van links naar rechts. InStrRev zoekt echter van rechts naar links.

We hebben bijvoorbeeld InStrRev gebruikt om de laatste punt in het e-mailadres te vinden. Het resultaat is 18, de positie van de punt vóór “com”.

Sub CheckChr()

    Dim result As Integer
    
    result = InStrRev("john.jacobs@email.com", ".")
    
    MsgBox result

End Sub

Je zult InStrRev nuttiger vinden wanneer je het laatste voorkomen nodig hebt of wanneer je werkt met bestandsnamen, e-mails of paden.

Veelvoorkomende toepassingen van VBA InStr

Nu we voorbeelden hebben bekeken van hoe de VBA-functie InStr werkt, laten we de veelvoorkomende use-cases bekijken.

Controleren of een cel een trefwoord bevat

Stel, je hebt een dataset met klantorders en je moet orders markeren voor specifieke afhandeling. De code bekijkt de kolom OrderNotes en markeert alles met “noon”.

Sub ChExist()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        If InStr(1, "Deliver before noon", "noon", vbTextCompare) > 0 Then
            ' Logic to mark as "Morning Delivery"
        End If
        
    Next i

End Sub

E-mailachtige tekst valideren

Je kunt de functie InStr ook gebruiken om snel geldige e-mailformaten te controleren. In de onderstaande code wordt bijvoorbeeld gecontroleerd of de e-mail @ bevat en wordt “Valid Email Format” in kolom 6 gezet.

Sub ValidateEmail()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        If InStr(Cells(i, 3).Value, "@") > 0 Then
            Cells(i, 6).Value = "Valid Email Format"
        End If
        
    Next i

End Sub

Scheidingstekens zoals komma’s of koppeltekens detecteren

Je kunt de functie InStr gebruiken om te controleren of er een koppelteken, komma of slash in tekst staat. In het voorbeeld hieronder doorloopt de code OrderNotes (kolom E), controleert of er een komma in de tekst staat en schrijft vervolgens een resultaat in kolom F.

Sub ValidateEmail()

    Dim i As Integer
    Dim commaPos As Integer
    Dim OrderNotes As String
    
    For i = 2 To 51
        
        OrderNotes = Cells(i, 5).Value
        
        commaPos = InStr(1, OrderNotes, ",")
        
        ' If a comma exists, mark it
        If commaPos > 0 Then
            Cells(i, 6).Value = "Has comma"
        Else
            Cells(i, 6).Value = "No comma"
        End If
        
    Next i

End Sub

Veelgemaakte fouten met VBA InStr

Zelfs als een ervaren ontwikkelaar kun je tegen problemen aanlopen met de functie InStr, waardoor je code zich onverwacht gedraagt. Dit zijn de veelvoorkomende issues die ik ben tegengekomen en hoe je ze oplost:

• Vergeten dat InStr 1-based is: Onthoud altijd dat posities bij 1 beginnen, niet bij 0. Als je uit Python of JavaScript komt, voelt dit in het begin vreemd.

• InStr als True/False behandelen: Vergeet niet dat InStr geen True of False teruggeeft. Als je iets wilt vinden, moet je checken of het resultaat > 0 is.

• Hoofdlettergevoeligheid negeren: Zoals je nu weet, is InStr standaard hoofdlettergevoelig. Als hoofdletters niet uitmaken, geef dat dan aan met vbTextCompare in het argument compare.

• string1 en string2 verwisselen: Onthoud altijd dat het eerste argument is waar je zoekt. Het tweede is wat je zoekt. Als je zo zoekt InStr(SearchTerm, FullText), krijg je altijd 0.

VBA InStr vs. andere VBA-tekstfuncties

Omdat InStr meestal laat zien waar een string in een tekst staat, vergelijken we hem met andere tekstfuncties.

InStr vs. Like

De operator Like wordt gebruikt voor patroonmatching. In de onderstaande code wordt bijvoorbeeld gecontroleerd op “Fragile”, maar met een patroon *, wat “alles ervoor of erna” betekent.

Sub ChExist()

    Dim i As Integer
    
    ' Loop through rows 2 to 51
    For i = 2 To 51
    
        If Cells(i, 5).Value Like "*Fragile*" Then
            Cells(i, 6).Value = "Handle Carefully"
        End If
        
    Next i

End Sub

Gebruik InStr als je alleen wilt weten of een tekst bestaat, en Like wanneer je tekst in complexe patronen wilt controleren.

InStr vs. Replace

Hoewel je InStr kunt gebruiken om de hele waarde in een cel te wijzigen, verandert de functie Replace specifieke tekst in de cel.

In het onderstaande voorbeeld vervangt de functie Replace direct “Fragile” door “Handle Carefully” in de tekst.

Sub ReplaceOrder()

    Dim i As Integer
    
    For i = 2 To 51
        Cells(i, 5).Value = Replace(Cells(i, 5).Value, "Fragile", "Handle Carefully")
    Next i

End Sub

InStr vs. Mid

De functie Mid wordt gebruikt om tekst uit een string te halen. Daarom gebruik je vaak Mid met InStr om de tekst te extraheren nadat je de positie hebt bepaald. 

De onderstaande code extraheert het domein uit e-mails in de dataset. De functie InStr vindt waar "@" in de e-mail verschijnt, daarna start Mid de extractie erna. De code eindigt met het schrijven van het domein in kolom H.

Sub ExtractDomain()

    Dim i As Integer
    Dim pos As Integer
    Dim domain As String
    
    For i = 2 To 51
        
        pos = InStr(1, Cells(i, 3).Value, "@")
        
        If pos > 0 Then
            domain = Mid(Cells(i, 3).Value, pos + 1)
            Cells(i, 8).Value = domain
        End If
        
    Next i

End Sub

InStr vs.Split

De functie Split wordt gebruikt om tekst op te delen in delen door een scheidingsteken op te geven. 

In het onderstaande voorbeeld leest de code elke rij uit kolom A, controleert of er een komma staat en splitst de tekst dan in twee delen. Hij schrijft Name → kolom B en City → kolom C.

Sub SplitTextColumn()

    Dim i As Integer
    Dim lastRow As Integer
    Dim parts As Variant
    Dim text As String
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lastRow
        
        text = Cells(i, 1).Value
        
        ' Only split if a comma exists
        If InStr(1, text, ",") > 0 Then
            
            parts = Split(text, ",")
            
            Cells(i, 2).Value = parts(0) ' Name
            Cells(i, 3).Value = parts(1) ' City
            
        End If
        
    Next i

End Sub

Best practices voor het gebruik van VBA InStr

Om fouten in je VBA te voorkomen, raad ik de volgende best practices aan, die mij tijd hebben bespaard:

• Gebruik vbTextCompare als hoofdletters niet uitmaken: Omdat de meeste echte data niet consistent is in hoofd-/kleine letters, kun je matches missen als je vertrouwt op het standaard compare-argument.

• Combineer met “If” voor duidelijke logica: Probeer altijd een logica toe te voegen om een waarde terug te geven, want InStr op zichzelf geeft je alleen een getal.

• Controleer op > 0, niet op exacte posities, tenzij je de positie echt nodig hebt.

• Houd parsing leesbaar. Stapel niet te veel functies op één regel.

Conclusie

InStr is simpel, maar je gebruikt het overal. Het geeft je de positie van de ene string binnen een andere, en dat ene getal stuurt veel beslissingen in VBA: of iets bestaat, waar je moet extraheren en wat een macro daarna moet doen. Op zichzelf is het slechts een zoekactie. In combinatie met functies als Mid, Replace of zelfs Split wordt het onderdeel van een workflow die daadwerkelijk werk verzet.

Volg onze cursus Data Analysis in Excel om geavanceerde analyses onder de knie te krijgen. Volg ook onze cursus Intermediate Power Query in Excel om te leren over datatransformatie en het gebruik van de M-taal.