Regex Extract word between Colon's

Dtex20

Board Regular
Joined
Jan 29, 2018
Messages
50
Hi Guys,

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

Code:
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:

Code:
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,056
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi there. Providing your "word" doesn't itself contain any colons, this code should extract the word for you (I havent tested it though):
Code:
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
 
Last edited:

Forum statistics

Threads
1,136,286
Messages
5,674,856
Members
419,530
Latest member
undisclosed

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top