代码之家 › 专栏 › 技术社区 › sushi

如果出现在字符串开头,则用VBA Excel替换数字的后2位

pattern-matching vba excel regex

sushi · 技术社区 · 6 年前

我想把数字的后两位数替换为“ XX BLOCK “如果它出现在字符串的开头并且超过2位。

我正在使用 Microsoft vbscript正则表达式5.5 参考文献。

Dim regEx As New RegExp
With regEx
    .Global = True 'Matches whole string, not just first occurrence
    .IgnoreCase = True 'Matches upper or lowercase
    .MultiLine = True 'Checks each line in case cell has multiple lines
    .pattern = "^(\d{2,})" 'Checks beginning of string for at least 2 digits
End With

'cell is set earlier in code not shown, refers to an Excel cell
regEx.replace(cell.Value, "XX BLOCK")

预期结果:

"1091 foo address"      --> "10XX BLOCK foo address"
"1016 foo 1010 address" --> "10XX BLOCK foo 1010 address"
"foo 1081 address"      --> "foo 1081 address"
"10 bar address"        --> "XX BLOCK bar address"
"8 baz address"         --> "8 baz address"

我是雷杰克斯的新手,不知道从哪里开始。我试着用 ^(\d{2,}) 但它会取代整个数字。

还可以保证号码(如果存在)将总是后面是空白。

2 回复 | 直到 6 年前

Wiktor StribiÅ¼ew 6 年前

您可以使用

^(\d*)\d{2}\b

或者,如果不能依赖单词边界,也可以使用

^(\d*)\d{2}(?!\d) ' no digit is allowed after the 2-digit sequence
^(\d*)\d{2}(?!\S) ' a whitespace boundary

替换为 $1XX BLOCK .

见 regex demo .

细节

^ -字符串开头
(\d*) -第1组:零位或多位
\d{2} 两位数
\b -单词边界,无数字、字母或 _ 允许在两位数之后
(?!\d) -如果在当前位置的右边有一个数字,则为负的向前看失败。
(?!\S) -如果当前位置右侧有一个非空白字符,则为一个否定的前瞻性,该前瞻性将使匹配失败。

0m3r 6 年前

https://regex101.com/r/M1QrPZ/1

Pattern = "^\d{2}(\d{2})"

尝试以下操作

Option Explicit
Private Sub Example()
    Dim RegExp As New RegExp
    Dim Pattern As String
    Dim rng As Range
    Dim Cel As Range

    Set rng = ActiveWorkbook.Sheets("Sheet1" _
                            ).Range("A1", Range("A9999" _
                            ).End(xlUp))


    Dim Matches As Variant
    For Each Cel In rng
        DoEvents
        Pattern = "^\d{2}(\d{2})"

        If Pattern <> "" Then
            With RegExp
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = Pattern
                 Set Matches = .Execute(Cel.Value)
            End With

            If Matches.Count > 0 Then
                Debug.Print Matches(0) ' full mach
                Debug.Print Matches(0).SubMatches(0) ' sub match
               Cel.Value = Replace(CStr(Cel), Matches(0).SubMatches(0), "XX BLOCK")
            End If
        End If

    Next
End Sub