VBA RegEx extracting data from within a string

Question

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.

brettdj · Accepted Answer · 2015-03-14 06:05:39Z

2

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.

edited Mar 14, 2015 at 6:05

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

answered May 21, 2013 at 17:17

fourpastmidnight

4,3041 gold badge39 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Geoff Over a year ago

Well, the second one didn't work but the first one did if I altered it like this .*(SB\s*\d{3,}).* but I don't see how that is really any different from what I had so why is it working??? I used the first one straight up but all it did was replace the text I wanted with $1 so I added the () but then it was setting the value to the whole line so I added the .* on the outsides.

fourpastmidnight Over a year ago

Ah, ok, that makes sense. The reason why the first is not working (I think) is the following: \d{3}\d+. I think the matching for that part may have been ambiguous?? At least with \d{3,}, what you're saying is that you must match a minimum of 3 digits. Actually, I just tested \d{3}\d+ in my Regex tester and indeed, it doesn't match. For Visual Studio 2010/2012, you should install the Regular Expression Tester extension--it's a great tool!

Collectives™ on Stack Overflow

VBA RegEx extracting data from within a string

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related