Challenge to find exact text string match

In the four rows of text below I'm trying to match each gas stick to its corresponding gasline.

I think I can count over to "GAS STICK" and get the number. <15>
From that point I can count over to "," (comma) and get that number. <26>
So the gas stick will be between the first number and one less than the second number. <GAS STICK 1>

I need help completing this code to place a the line or stick number in a cell B of the same row.
Column A would have the description and column B of the same row would either be "1" or "11" respectively.
Column C would be the shortened description (GAS STICK 1).


1] "HUNGA, DUNGA, GAS STICK 1, THINGY"
2] "DOHICKY, GAS STICK 11, THINGUS"
3] "SCAPPEN, DAPPER, GASLINE 1"
4] "SUCH, AND, SUCH, GASLINE 11"

Sub test()

Dim MainWS As Worksheet
Dim FirstNum As Integer
Dim LastNum As Integer
Dim ParentSTR As String
Dim ActiveRow As Long
Dim ActiveCol As Long
Dim LastRow As Long

LastRow = MainWS.Cells(Rows.Count, 1).End(xlUp).Row
ActiveCol = 1 'Column A

For ActiveRow = 2 To LastRow

  ParentSTR = Cells(ActiveRow, ActiveCol).Value

  FirstNum = InStr(1, ParentSTR, "GAS STICK")
  LastNum = InStr(FirstNum, ParentSTR, ",")
  LastNum = LastNum -1

Next ActivRow

End Sub

Try:

Sub RunMe()
Dim ws As Worksheet:    Set ws = Sheets("Sheet1")
Dim rCell As Range
Dim arrSplit() As String
Dim lSplit As Long

For Each rCell In ws.Range("A2:A" & ws.Range("A" & Rows.Count).End(xlUp).Row)
    If Not rCell.Value = "" Then
        arrSplit = Split(rCell.Value, ",")
            For lSplit = LBound(arrSplit) To UBound(arrSplit)
                If Not InStr(1, arrSplit(lSplit), "GASLINE") = 0 Then
                    If IsNumeric(Right(arrSplit(lSplit), 2)) Then
                        ws.Range("B" & rCell.Row).Value = Right(arrSplit(lSplit), 2)
                        ws.Range("C" & rCell.Row).Value = arrSplit(lSplit)
                    End If
                End If
                If Not InStr(1, arrSplit(lSplit), "GAS STICK") = 0 Then
                    If IsNumeric(Right(arrSplit(lSplit), 2)) Then
                        ws.Range("B" & rCell.Row).Value = Right(arrSplit(lSplit), 2)
                        ws.Range("C" & rCell.Row).Value = arrSplit(lSplit)
                    End If
                End If
            Next lSplit
    End If
Next rCell

End Sub

Heres another..

Private Sub CommandButton1_Click()
  Dim n, i As Long
    With CreateObject("vbscript.regexp")
        .Pattern = "(GAS STICK |GASLINE )\d+"
        For i = 3 To Range("A" & Rows.Count).End(xlUp).Row
            Set myMatches = .Execute(Cells(i, 1))
            For Each n In myMatches
            If UBound(Split(n, " ")) > 1 Then
                Cells(i, Range("IV" & i).End(xlToLeft).Column).Offset(, 1).Resize(, 2).Value = Array(Split(n, " ")(2), n)
                Else
                Cells(i, Range("IV" & i).End(xlToLeft).Column).Offset(, 1).Resize(, 2).Value = Array(Split(n, " ")(1), n)
                End If
            Next n
        Next i
    End With
End Sub