CODEWITHSUNDEEP
regexvba
Geoff
Geoff

Reputation: 256

VBA RegEx extracting data from within a string

I am new to RegEx and I can't seem to figure out why this is not working. I had it working but lost my data. I have tried all sorts of variations and I can't remember what I did to get this to work before.

I have fields with the following variations

"SB123"
"some data SB 1234"
"some data SB 1234567 and more data"
"SB1234." etc...

and what I would like to extract is the SB and number portion. Can someone please help? 

Sub ExtractSBData()

Dim regEx
Dim i As Long
Dim pattern As String
Set regEx = CreateObject("VBScript.RegExp")
regEx.IgnoreCase = True
regEx.Global = True
regEx.pattern = ".*(SB\s*\d{3}\d+).*"

    For i = 2 To ActiveSheet.UsedRange.Rows.Count
        If (regEx.test(Cells(i, 14).value)) Then
            Cells(i, 16).value = regEx.Replace(Cells(i, 14).value, "$1")
        End If
    Next i

End Sub

Sorry, I realized I didn't explain what it's currently doing. Currently it is only working if the grouping is surrounded by data (ie "data here SB123 and here but not "SB123 data here" or "data here SB123"). In addition, it is putting the whole line in cells(i, 16) instead of just the SB number portion.

Upvotes: 1

Views: 4426

Answers (1)

fourpastmidnight
fourpastmidnight

Reputation: 4234

You may just want to try SB\s*\d{3,}.

If that doesn't work (because of data preceding or following the text you're looking for), remember that the . operator in RegEx matches every character except \n (newline). Then you could try [.\n]*SB\s*\d{3,}[.\n]*.

Let me know if one of those work for you.

Upvotes: 2

Related Questions