正则表达式使用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屋！