正则表达式使用Visual Basic函数替换Excel中的范围 [英] Regex Match & Replace on Range in Excel using Visual Basic Function
问题描述
我想在Excel(2010,Visual Basic 7)中创建一个名为CODEMATCH的用户定义函数,它将
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 ""
我的痛苦之处在于我是Visual Basic的新手。我认为,我的痛苦的一部分来自Visual Basic中存在的RegEx的多个版本,并且不知道哪个版本需要什么属性。
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
代码正常工作,但不会让我使用 matchPattern
。除此之外,我仍然需要采用它来执行正则表达式替换,并采用它来处理范围而不是单个单元格。
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.
推荐答案
怎么样:)
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
测试
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
这篇关于正则表达式使用Visual Basic函数替换Excel中的范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!