Excel想用正则表达式将1.4K取代为1400
Admin |
2017-6-1 8:34:12 | ReadNums |
421 | 标签
Excel教程 |
打印本页
Dim LastRow As Long, i As Long, j As Long, Num As Long
Dim Str As String, Str1 As String, Str2 As String
Dim Regx As Object, Mat As Object
Dim Arr
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Arr = Range("A2:A" & LastRow)
Set Regx = CreateObject("vbscript.regexp")
For i = 1 To UBound(Arr)
Str = Arr(i, 1)
With Regx
.Global = True
.Pattern = "[\d\.]+[kK]"
Set Mat = .Execute(Str)
For j = 0 To Mat.Count - 1
Str1 = Mat(j).Value
Num = Val(Left(Str1, Len(Str1) - 1)) * 1000
Str2 = "" & Num
Str = Replace(Str, Str1, Str2)
Next j
End With
Arr(i, 1) = Str
Next i
Range("B2").Resize(UBound(Arr)) = Arr
网友评论
- （访客）
- 内容实用原创，讲得很好。
- 20xx年x月x日
- （站长）
- 有问题请在线咨询。
- 20xx年x月x日
我要在线咨询问题
