Regex Match & Replace on Range in Excel using Visual Basic Function
I am looking to create a custom 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. I think part of my agony comes from multiple versions of RegEx that exist in Visual Basic and don't know which version is taking which properties.
I tried to build the function up the difficulty level, and this is as far as I can get before I hit the impenetrable brick wall:
Function CODEMATCH(ByVal valueIN As String) As String
Set matchRegEx = New RegExp
matchRegEx.Pattern = "(sdi \d+)" '<--what 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 how it works, but it won't let me use the matchPattern
one I defined earlier. Also, I still need to accept it in order to do the regex replacement and accept it to handle ranges, not individual cells.
+3
source to share
1 answer
Like 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
+1
source to share