如何使用VBA正则表达式替换来更改匹配字母的大小写? [英] How to change case of matching letter with a VBA regex Replace?

问题描述

我有一列如下所示的代码.

I have a column of lists of codes like the following.

2.A.B, 1.C.D, A.21.C.D, 1.C.D.11.C.D
6.A.A.5.F.A, 2.B.C.H.1
8.ABC.B, A.B.C.D
12.E.A, 3.NO.T
A.3.B.C.x, 1.N.N.9.J.K

我想找到两个以句点分隔的单个大写字母的所有实例，但仅找到数字小于6的那些实例.我想删除字母之间的句点并将第二个字母转换为小写.所需的输出:

I want to find all instances of two single upper-case letters separated by a period, but only those that follow a number less than 6. I want to remove the period between the letters and convert the second letter to lower case. Desired output:

2.Ab, 1.Cd, A.21.C.D, 1.Cd.11.C.D
6.A.A.5.Fa, 2.Bc.H.1
8.ABC.B, A.B.C.D
12.E.A, 3.NO.T
A.3.Bc.x, 1.Nn.9.J.K

我在VBA中有以下代码.

I have the following code in VBA.

Sub fixBlah()
Dim re As VBScript_RegExp_55.RegExp
Set re = New VBScript_RegExp_55.RegExp
re.Global = True
re.Pattern = "\b([1-5]\.[A-Z])\.([A-Z])\b"
For Each c In Selection.Cells
    c.Value = re.Replace("$1$2")
Next c
End Sub

这删除了句点，但不满足小写要求.我知道在其他形式的正则表达式中，我可以使用类似

This removes the period, but doesn't handle the lower-case requirement. I know in other flavors of regular expressions, I can use something like

re.Replace("$1\L$2\E")

但是在VBA中没有达到预期的效果.我尝试使用谷歌搜索功能，但找不到任何东西.有没有办法在VBA中使用简单的re.Replace()语句来做到这一点?

but this does not have the desired effect in VBA. I tried googling for this functionality, but I wasn't able to find anything. Is there a way to do this with a simple re.Replace() statement in VBA?

如果没有，我将如何实现这一目标?模式匹配非常复杂，以至于我什至不想考虑没有正则表达式的情况.

If not, how would I go about achieving this otherwise? The pattern matching is complex enough that I don't even want to think about doing this without regular expressions.

[我有一个解决方案，发布在下面，但我希望有人可以提出一些更简单的方法.]

[I have a solution I worked up, posted below, but I'm hoping someone can come up with something simpler.]

推荐答案

所以这个问题很旧，但是我确实有另一个解决方法.可以这么说，我使用双正则表达式，其中第一个引擎将查找的匹配项作为执行，然后遍历所有这些项并替换为小写版本.例如:

So this question is old, but I do have another workaround. I use a double regex so to speak, where the first engine looks for the match as an execute, then I loop through each of those items and replace with a lowercase version. For example:

Sub fixBlah()
Dim re As VBScript_RegExp_55.RegExp
dim ToReplace as Object
Set re = New VBScript_RegExp_55.RegExp
for each c in Selection.Cells
with re      `enter code here`
  .Global = True
  .Pattern = "\b([1-5]\.[A-Z])\.([A-Z])\b"
  Set ToReplace = .execute(C.Value)
end with

 'This generates a list of items that match.  Now to lowercase them and replace
 Dim LcaseVersion as string
 Dim ItemCt as integer
 for itemct = 0 to ToReplace.count - 1
 LcaseVersion = lcase(ToReplace.item(itemct))
      with re      `enter code here`
  .Global = True
  .Pattern = ToReplace.item(itemct)  'This looks for that specific item and replaces it with the lowercase version
  c.value = .replace(C.Value, LCaseVersion)
end with
End Sub

我希望这会有所帮助！

这篇关于如何使用VBA正则表达式替换来更改匹配字母的大小写?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！