Can I use regex to not only identify a pattern but extract the value found?

Question

I am using Excel VBA.

I need to extract the dimensions (width x height) of a creative from a string and the dimensions will always be in the format:

000x000 or 000X000 or 000x00 or 000X00 where 0 can be any number between 1-9 and x can be upper or lower case.

I read this guide: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

And I think what I want is something similar to:

[0-9]{2, 3}[xX][0-9]{2, 3}

So if my string is:

creativeStr = ab234-cdc-234-300x250-777aabb

I want to extract "300x250" and assign it to a variable like this:

dimensions = 300x250

Is my Regex above correct? Also, how would I pull the resulting match into a variable?

Here is part of my code:

creativeStr = "Sample-abc-300x250-cba-123"
regex_pattern = "[0-9]{2,3}[xX][0-9]{2,4}"

If regex_pattern <> "" Then

    With regEx
        .Global = True
        .Pattern = regex_pattern
    End With

    If regEx.Test(creativeStr) Then
        dimensions = regEx.Replace(creativeStr, "$1")
    Else
        dimensions = "Couldn't extract dimensions from creative name."
    End If

End If

But it still returns the condition in my else clause...

Thanks!

I updated my code to show what I am using currently.. but I still can't figure out how to extract the dimensions and the unhelpful mod closed my issue after referencing articles I already viewed but didn't understand fully -_-. — Kamui, Nov 25 '19 at 22:19

score 0 · Accepted Answer · answered Nov 26 '19 at 02:53

Your examples do not match your regex. Your examples show that the first set of digits will always be three, and the last set either two or three.

Also, in your description you write can be any number between 1-9 but your example includes 0's.

If you are going to work with regex, that type of imprecision will lead to undesired results.

Asssuming that 0's should be included, and that the desired pattern is 3x2 or 3x3, then perhaps this example will provide some clarity:

Option Explicit
Function dimension(S As String) As String
    Dim RE As Object, MC As Object
    Const sPat As String = "[0-9]{3}[Xx][0-9]{2,3}"
    ' or, with .ignorecase = true, could use:  "\d{3}x\d{2,3}"
Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = sPat
    If .Test(S) = True Then
        Set MC = .Execute(S)
        dimension = MC(0)
    Else
        dimension = "Couldn't extract dimensions from creative name."
    End If
End With

End Function

Sub getDimension()
  Dim creativeStr As String
  Dim Dimensions As String
creativeStr = "Sample-abc-300x250-cba-123"

Dimensions = dimension(creativeStr)

Debug.Print Dimensions

End Sub

Can I use regex to not only identify a pattern but extract the value found?

1 Answers1