VBA Excel如果在字符串开头出现数字，请替换数字的最后两位 [英] VBA Excel Replace last 2 digits of number if occurs at beginning of string

问题描述

如果数字的最后两位出现在字符串的开头并且超过两位，我正在尝试用" XX BLOCK "替换数字的最后两位.

I'm trying to replace the last two digits of number with "XX BLOCK" if it occurs at the start of the string and has more than 2 digits.

我正在使用 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"

我是regex的新手，不确定从哪里开始.我尝试使用 ^(\ d {2，})，但随后它替换了整个数字.

I'm new to regex and not sure where to start. I tried using ^(\d{2,}) but then it replaces the entire number.

还保证数字(如果存在)将始终 后跟空格.

There is also a guarantee that the number (if exists) will always be followed with a white space.

推荐答案

您可以使用

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

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

Or, if you cannot rely on a word boundary, you may also use

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

并替换为 $ 1XX BLOCK .

请参见 regex演示.

详细信息

• ^ -字符串的开头
• (\ d *)-第1组:零个或多个数字
• \ d {2} -两位数字
• \ b -单词边界，两个数字之后不得有数字，字母或 _
• (?！\ d)-如果当前位置的右侧紧邻有一个数字，则负向超前将导致匹配失败
• (?！\ S)-如果当前位置右侧紧邻有一个非空格字符，则负匹配将使匹配失败.

• ^ - start of string
• (\d*) - Group 1: zero or more digits
• \d{2} - two digits
• \b - a word boundary, no digit, letter or _ is allowed right after the two digits
• (?!\d) - a negative lookahead that fails the match if there is a digit immediately to the right of the current location
• (?!\S) - a negative lookahead that fails the match if there is a non-whitespace char immediately to the right of the current location.

这篇关于VBA Excel如果在字符串开头出现数字，请替换数字的最后两位的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！