Excel VBA + Regular Expression

Question

Ok, to start. I'm a little rusty on VBA, 3 + years since Ive need to use it.

In short, im struggling to extract text from a string. Im using regular expression to extract my department name and date from this string.

The Department will always fall between : and -.

I can't share the document due to security. But, I can explain the format and hopefully we can work from that.

Col A----Col B----Col C---Col D

Date(e)--Dept(e)--String--Duration

Where (e) means it was extracted from the string.

My code for the extraction, thus far, is below. Currently it will loop through all available rows and extract the department, but it always take the : and - with it! I can't seem to find a way to cut these out.

Any assistance?

I can probably work out the date bit eventually.

The final output from this code is ": Inbound Contacts -" Where I need, "Inbound Contacts".

Sub stringSearch()
    Dim ws As Worksheet
    Dim lastRow As Long, x As Long
    Dim matches As Variant, match As Variant
    Dim Reg_Exp As Object

    Set Reg_Exp = CreateObject("vbscript.regexp")

    Reg_Exp.Pattern = "\:\s(\w.+)\s\-"


    Set ws = Sheet2

    lastRow = ws.Range("C" & Rows.Count).End(xlUp).Row

    For x = 1 To lastRow
        Set matches = Reg_Exp.Execute(CStr(ws.Range("C" & x).Value))
        If matches.Count > 0 Then
            For Each match In matches
                ws.Range("B" & x).Value = match.Value
            Next match
        End If
    Next x
End Sub

Answer 1

This is how to achieve what you want without regex, in general it should be a bit faster and way more understandable:

Sub TestMe()

    Dim inputString As String
    inputString = "Planning Unit: Inbound Contacts = Tuesday, 27/03/2018"
    Debug.Print Split(Split(inputString, ":")(1), "=")(0)

End Sub

• split the inputString by : and take the second part;
• split the taken part by = and take the first part;