VBA Regex

[vegard_fv]

Hi, I am new to Regex, and struggeling to learn. In the meantime I reach out in hope for help.

I am working with an excel-app that sends out requests for public information (FOIA) to public offices. The requests does always contain the case and documentnumber that identifies the documents at the public office. They are always in formats like this:

2016/4408-95 KS Regnskapsundersøkelse 2016


Dato28.02.2017SakOslo kommunes regnskap 2016FraKS

2017/698-4 15/2017 Spørsmål om tilknytningsavgiften for vann og avløp


Dato27.02.2017SakTilknytningsavgiftene for vann og avløpTilFinanskomiteen

2016/4928-3 14/2017 Verbalvedtak og takster fra bystyrets behandling av budsjett 2017


Dato27.02.2017SakOppfølging av budsjett 2017TilFinanskomiteen

2016/6013-2 16/2017 Spørsmål til byrådet fra Peter Myhre(F) - Budsjettbehandling


Dato27.02.2017SakKostnader ved budsjettbehandlingenTilFinanskomiteen

2017/1115-2 Etterlyser urevidert regnskap - innsyn


Dato24.02.2017SakInnsyn i regnskapet - økonomisk oversikt drift, investering og balanse 2016FraKommunal rapport AS

2016/2167-11 Oversendelse av uavhengig revisors beretning om kompensasjonsoppgave for merverdiavgift for 6. termin 2016

So thats: always 4 digits identifying the year, and then always "/", followed by x number of numbers, then "-" and finally x numbers of numbers.

My excel-app extraxts each of this posts as one text-string. Could any help me write the regex - snippet I need to extract the casenumbers? The reason that I need Regex for it, is that they may appear in the middle of the text as well.

Any help much appreciated

[Paul_Hossler]

Maybe something like this

It only returns the first one, but can be expanded to return all 'Finds' in an array

Option Explicit
Sub drv()
    Dim s As String
    
    s = "2016/2167-11 Oversendelse av uavhengig revisors beretning om kompensasjonsoppgave for merverdiavgift for 6. termin 2016"
    MsgBox RegExpExtract(s)

    s = "Oversendelse av uavhengig revisors beretning om 2016/2167123456-1122334455 kompensasjonsoppgave for merverdiavgift for 6. termin 2016"
    MsgBox RegExpExtract(s)
End Sub

Function RegExpExtract(LookIn As String) As String
    Dim oRegExp As Object
    'Create instance of RegExp object
    Set oRegExp = CreateObject("VBScript.RegExp")
    With oRegExp
        .Pattern = "\d{4}/\d+-\d+"
        .Global = True
        .IgnoreCase = False
    End With
     
    'Test to see if there are any matches
    If oRegExp.test(LookIn) Then
         'Run RegExp to get the matches, which are returned as a zero-based collection
        RegExpExtract = oRegExp.Execute(LookIn)(0)
    Else
        RegExpExtract = vbNullString
    End If

    Set oRegExp = Nothing

End Function

[vegard_fv]

Great, it works! Tthanks for giving me great head start. Super - cool!
And I'll need to work on my Regex.