regex - 使用 REGEX 进行大小写转换

Question

在 VBA 中使用 Excel 和 REGEX，我是新手。

我需要通过 strReplace 命令将一个捕获组的内容从全部大写转换为标题。我不能对 strReplace 的结果使用 Excel PROPER 函数，因为它没有提供所需的结果。

示例单元格内容是(并非所有单元格都像这样，这就是字符串模式定义更具包容性的原因):

FDI850-4224 JIM SMITH 29 HP (21.6 kw)

我的字符串模式是:

strPattern = "^(\D{2,4}\d{0,4})-(\d{1,4}) (.*)\s(\d\d)( HP)\s\((\d\d.\d)\skw\)"

我的替换模式是:

strReplace = "$1-$2 - Juniper $1 Part, $3, $4HP, $6KW"

放置在相邻单元格中的当前结果是:

FDI850-4224 - Juniper FDI850 Part, JIM SMITH, 29HP, 21.6KW

我希望放置在相邻单元格中的结果是:

FDI850-4224 - Juniper FDI850 Part, Jim Smith, 29HP, 21.6KW

是否可以在 strReplace 中使用捕获组 3 (.*) 将其从全部大写更改为标题？

这是我的 VBA 代码:

Function tom_test(Myrange As Range) As String

Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String

strPattern = "^(\D{2,4}\d{0,4})-(\d{1,4}) (.*)\s(\d\d)( HP) \((\d\d.\d\d)\skW\)"

If strPattern <> "" Then
    strInput = Myrange.Value
    strReplace = "$1-$2 - Juniper $1 Part, $3, $4HP, $6KW"
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.Test(strInput) Then
        tom_test = regEx.Replace(strInput, strReplace)

    Else
        tom_test = "No Bueno"
    End If
End If    
End Function

我尝试了从 REGEX 函数获取结果然后在其上使用 Excel PROPER 函数的两步过程，但它混淆了其他捕获组结果的结果:
以下是对 strReplace 的结果使用 PROPER 后发生的情况:

Fdi850-4224 - Juniper Fdi850 Part, Jim Smith, 29Hp, 21.6Kw

我将不胜感激任何帮助。

Answer 1

尝试这个:

Option Explicit
Function tom_test(Myrange As Range) As String

Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String

Dim MC As MatchCollection

strPattern = "^(\D{2,4}\d{0,4})-(\d{1,4}) (.*)\s(\d\d)( HP)\s\((\d\d.\d)\skw\)"

If strPattern <> "" Then
    strInput = Myrange.Value
    strReplace = "$1-$2 - Juniper $1 Part, $3, $4HP, $6KW"
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.Test(strInput) Then
        Set MC = regEx.Execute(strInput)

        strReplace = Replace(strReplace, "$3", StrConv(MC(0).SubMatches(2), vbProperCase))

        tom_test = regEx.Replace(strInput, strReplace)

    Else
        tom_test = "No Bueno"
    End If
End If
End Function