Regex Match & Replace on Range in Excel using Visual Basic Function

Question

I'm looking to create a user-defined function in Excel (2010, Visual Basic 7) called CODEMATCH that will

1. Take a range as an input
2. Preserve the structure of the range when outputting
3. On each value in the range:
   a. If the value matches [matchPattern]:
      i. regex match the value against [matchPattern] and store it as [var1]
      ii. regex replace [var1] against [stripPattern] and store it as [var2]
      iii. return [var2]
  b. If the value does not match [matchPattern]:
      i. return an empty value

 Where
   matchPattern = "^[^A-Z0-9:]*[A-Z0-9][^A-Z0-9:]*[A-Z0-9]?"
   stripPattern = "[^A-Z0-9]*"

 AndWhere
   RegEx match is not global and respects case
   RexEx replace is global and respects case

 Such that
   "Nobody Cares about Bob" returns "NC"
   "1 Duck for Jody" returns "1D"
   "Apples: I Don't Like Them" returns "A"
   "foobar" returns ""

Part of my agony is that I'm new to Visual Basic. Part of my agony, I think, comes from multiple versions of RegEx existing in Visual Basic and not knowing which version takes what properties.

I tried building the function up in level of complexity, and this is as far as I could get before I hit an impenetrable brick wall:

Function CODEMATCH(ByVal valueIN As String) As String

Set matchRegEx = New RegExp
    matchRegEx.Pattern = "(sdi \d+)"  '<--what's giving me difficulty
    matchRegEx.Global = False
    matchRegEx.IgnoreCase = False

Set matches = matchRegEx.Execute(valueIN)

If matches.Count <> 0 Then
    CODEMATCH = matches.Item(0).SubMatches.Item(0)
Else
    CODEMATCH = ""
End If

End Function

The code as it is works, but it won't let me use the matchPattern that I defined earlier. Beyond that, I still need to adopt it to perform a regex replace and adopt it to handle ranges rather than single cells.

Is it just me, or would var1=regex.Match(valueIn, matchPattern); var2=regex.Replace(var1, stripPattern, "") be too obvious? — Brandon Lebedev
– Brandon Lebedev, Commented Jan 2, 2015 at 22:00
Why not just pass matchPattern As String as another input to your function and use it with matchRegEx.Pattern = matchPattern? — TheEngineer
– TheEngineer, Commented Jan 2, 2015 at 22:09
See below, I used a two step process, an Execute for your match, and a Replace for your strip pattern. No submatches needed — brettdj
– brettdj, Commented Jan 3, 2015 at 13:50

brettdj · Accepted Answer · 2015-01-03 13:45:41Z

1

How this? :)

Function CODEMATCH(ByVal valueIN As String) As String

Dim strTemp As String
Set matchRegEx = New RegExp
With matchRegEx
    .Pattern = "[^A-Z0-9:]*[A-Z0-9][^A-Z0-9:]*[A-Z0-9]?"
    .Global = False
    .IgnoreCase = False
If .Test(valueIN) Then
    Set matches = .Execute(valueIN)
    .Pattern = "[^A-Z0-9]*"
    .Global = True
    strTemp = matches(0)
    CODEMATCH = .Replace(strTemp, vbNullString)
Else
 CODEMATCH = vbNullString
End If
End With

End Function

test

Sub Test()
Debug.Print CODEMATCH("Nobody Cares about Bob")
Debug.Print CODEMATCH("1 Duck for Jody")
Debug.Print CODEMATCH("Apples: I Don't Like Them")
Debug.Print CODEMATCH("foobar")
End Sub

answered Jan 3, 2015 at 13:45

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

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

4 Comments

Brandon Lebedev Over a year ago

The code works beatifully... up to the point of being able to take a range, rather than a string, as an input; and output a range as well. I know it'll take more than just CODEMATCH(ByVal valueIN As Range) to output a range because of the CODEMATCH=, but I'm not sure how to do this.

brettdj Over a year ago

@BrandonLebedev if your data was in A1:A10 etc then in B1 put = CODEMATCH(A1) and copy down to B10. Or were you looking to do this programatically

Brandon Lebedev Over a year ago

The original specifications were for the function to input and output an array programatically

brettdj Over a year ago

An example of what you would like would be useful so this can be updated to suit.

Collectives™ on Stack Overflow

Regex Match & Replace on Range in Excel using Visual Basic Function

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related