EXCEL 自定义函数提取字符串中的数…

自定义函数提取字符串中的数字

 

FunctionGetNB(rng As Range)
If rng <> "" Then
    For i = 1 ToLen(rng)
       tmp = Mid(rng, i, 1)
       If IsNumeric(tmp) Or tmp Like "[+-\*\/^%)()]" And Not tmp Like"[A-Z?!~@=_,;|\[]" Then GetNB = GetNB & tmp
    Next
Else
    GetNB =""
End If
End Function

 

 

若使运算式自动计算，则用以下代码

 

FunctionCountNB(rng As Range)
If rng <> "" Then
For i = 1 To Len(rng)
    tmp =Mid(rng, i, 1)
    IfIsNumeric(tmp) Or tmp Like "[+-\*\/^%()]" And Not tmp Like"[A-Z?!~@=_,;|\[]" Then CountNB = CountNB &tmp
Next
    CountNB =Application.Evaluate(CountNB)
Else
    CountNB =""
End If
End Function