Excel想用正则表达式将1.4K取代为1400

Admin | 2017-6-1 8:34:12 | 被阅次数 | 5688

温馨提示!

如果未能解决您的问题，请点击搜索；登陆可复制文章，点击登陆

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

该杂文来自： Excel杂文

上一篇：EXCEL查找隐藏列的数据的方法

下一篇：EXCEL使用公式截取单元格右边的中文