Regex Extract word between Colon's

[Dtex20]

Hi Guys,

I have a two specific patterns i'm looking for, it is contained in the same string of a cell.

strPattern = "(^[G][0-9]{6})"

The string goes like this:

G000000 : "Word" : Rest of the details.

I'm trying to extract the "word" between the colons, it could be one or multiple words. I tried something along the lines of:

strPattern = "(^[G][0-9]{6}) [B](^[:][+*.?]$[:])[/B]"

With Sheets("Sheet1")
    For x = 2 To csvRow
       strInput = .Range("J" & x).Value ' BOM Description.
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With
        If regEx.Test(strInput) Then
            str = regEx.Execute(strInput)(0)
            strTwo = regEx.Execute(strInput) (1)
            MsgBox (str)
        End If
    Next
End With

Does anyone have any suggestions to extract the second part?

Thanks!

 

[jmacleary]

Hi there. Providing your "word" doesn't itself contain any colons, this code should extract the word for you (I havent tested it though):

Sub extracter()
With Sheets("Sheet1")
    For x = 2 To csvRow
       strInput = .Range("J" & x).Value ' BOM Description.
       starter = InStr(strInput, ":")
        ender = InStr(starter + 1, strInput, ":")
        Str = Mid(strInput, starter + 1, ender - starter - 1)
            MsgBox (Str)
        End If
    Next
End With
End Sub