2,462
社区成员
发帖
与我相关
我的任务
分享
Sub abc()
Dim c() As String
For i = 2 To 4
c = Split(Sheet1.Cells(i, 1).value, "/")
For j = 0 To UBound(c)
c(j) = RegReplace(c(j))
Next j
Sheet1.Cells(i, 2) = Join(c, "/")
Next
End Sub
Function RegReplace(plain As String)
Dim value As String, regx
value = plain
Set regx = CreateObject("VBSCRIPT.REGEXP")
regx.Pattern = "-*\d+\.*\d+?"
regx.Global = True
For Each Match In regx.Execute(value)
value = Replace(value, Match.value, (Match.value + 200))
Next
RegReplace = value
End Function
[/quote]
负小数用例还是不对
比如-1312.312 执行结果是 -11212.3212[/quote]
regx.Pattern = "-*\d+(\.\d+){0,}"
Sub abc()
Dim c() As String
For i = 2 To 4
c = Split(Sheet1.Cells(i, 1).value, "/")
For j = 0 To UBound(c)
c(j) = RegReplace(c(j))
Next j
Sheet1.Cells(i, 2) = Join(c, "/")
Next
End Sub
Function RegReplace(plain As String)
Dim value As String, regx
value = plain
Set regx = CreateObject("VBSCRIPT.REGEXP")
regx.Pattern = "-*\d+\.*\d+?"
regx.Global = True
For Each Match In regx.Execute(value)
value = Replace(value, Match.value, (Match.value + 200))
Next
RegReplace = value
End Function
[/quote]
负小数用例还是不对
比如-1312.312 执行结果是 -11212.3212Sub abc()
Dim c() As String
For i = 2 To 4
c = Split(Sheet1.Cells(i, 1).value, "/")
For j = 0 To UBound(c)
c(j) = RegReplace(c(j))
Next j
Sheet1.Cells(i, 2) = Join(c, "/")
Next
End Sub
Function RegReplace(plain As String)
Dim value As String, regx
value = plain
Set regx = CreateObject("VBSCRIPT.REGEXP")
regx.Pattern = "-*\d+\.*\d+?"
regx.Global = True
For Each Match In regx.Execute(value)
value = Replace(value, Match.value, (Match.value + 200))
Next
RegReplace = value
End Function
Regex.Match(@"<a href='abc' target='_blank'", "href='(.+)'.+target='(.+)'")
,匹配的结果里就有两个分组,分别是abc和_blank(也就是href部分和target部分)。而你的数据要匹配的是“不确定数量的数字”,每个匹配结果地位相等,没有结构位置上的差别,这只是普通的匹配(当然用分组也能做,将同一个分组做重复匹配,但是这其实是将分组当成普通匹配来用)。
VBA的正则有一个比较坑的地方就是不支持匿名函数,Regex.Replace没有办法像高级语言里直接对分组的匹配进行计算再替换结果(比如:Regex.Replace("Ab12C12D", @"\d+",match=> (Convert.ToInt32(match.Value) + 200).ToString()),在Replace里,将Match结果处理一下直接就能赋给“替换到”参数实现一句话替换),所以这儿先split再针对最小切割单元进行替换(也可能它确实支持,但我不懂)。
正则虽然写起来方便,但是如果是对大量数据进行处理,字符串遍历的性能要比正则高很多,所以不要一味迷信某一种特定的算法。
Public Sub Test()
Dim strTest As String
strTest = "小朱100.2/小刘-200/小海800/小混蛋-1312.312"
Debug.Print strTest
Debug.Print TranTest(strTest)
End Sub
Private Function TranTest(strinput As String) As String
Dim i As Long, j As Long
Dim strOutPut As String, strTmp As String
i = 1
Do While i <= Len(strinput)
If IsNumeric(Mid(strinput, i, 1) & "0") Then
j = i
strTmp = ""
Do While j <= Len(strinput) And IsNumeric(Mid(strinput, j, 1) & "0")
strTmp = strTmp & Mid(strinput, j, 1)
j = j + 1
DoEvents
Loop
strOutPut = strOutPut & Trim(Str(Val(strTmp) + 200))
i = j - 1
Else
strOutPut = strOutPut & Mid(strinput, i, 1)
End If
i = i + 1
DoEvents
Loop
TranTest = strOutPut
End Function
输出
小朱100.2/小刘-200/小海800/小混蛋-1312.312
小朱300.2/小刘0/小海1000/小混蛋-1112.312
Sub abc()
Dim c() As String
For i = 2 To 4
c = Split(Sheet1.Cells(i, 1).value, "/")
For j = 0 To UBound(c)
c(j) = RegReplace(c(j))
Next j
Sheet1.Cells(i, 2) = Join(c, "/")
Next
End Sub
Function RegReplace(plain As String)
Dim value As String, regx
value = plain
Set regx = CreateObject("VBSCRIPT.REGEXP")
regx.Pattern = "\d+"
regx.Global = True
For Each Match In regx.Execute(value)
value = Replace(value, Match.value, (Match.value + 200))
Next
RegReplace = value
End Function
Public Sub Test()
Dim strTest As String
strTest = "小朱100/小刘200/小海800"
Debug.Print strTest
Debug.Print TranTest(strTest)
End Sub
Private Function TranTest(strinput As String) As String
Dim i As Long, j As Long
Dim strOutPut As String, strTmp As String
i = 1
Do While i <= Len(strinput)
If IsNumeric(Mid(strinput, i, 1)) Then
j = i
strTmp = ""
Do While j <= Len(strinput) And IsNumeric(Mid(strinput, j, 1))
strTmp = strTmp & Mid(strinput, j, 1)
j = j + 1
Loop
strOutPut = strOutPut & Trim(Str(Val(strTmp) + 200)) '识别出数字加200
i = j - 1
Else
strOutPut = strOutPut & Mid(strinput, i, 1)
End If
i = i + 1
'DoEvents
Loop
TranTest = strOutPut
End Function
输出结果:
小朱100/小刘200/小海800
小朱300/小刘400/小海1000
这段代码目前对负数和小数的处理不对,看楼主需求,有需求再改。
如果输入全是正整数,就OK了。