从Excel中的混合字母数字文本字符串中提取数字

Excel公式提取字符串中的数字

=IF(SUM(LEN(A2)-LEN(SUBSTITUTE(A2, {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”}, “”)))>0, SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(–MID(A2,ROW(INDIRECT(“$1:$”&LEN(A2))),1))* ROW(INDIRECT(“$1:$”&LEN(A2))),0), ROW(INDIRECT(“$1:$”&LEN(A2))))+1,1) * 10^ROW(INDIRECT(“$1:$”&LEN(A2)))/10),””)

VBA代码：从单元格中删除所有非数字字符

Sub GetNumbers()
'Updated by Extendoffice 20210125
    Dim xRegEx As Object
    Dim xRg As Range
    Dim xCell As Range
    Dim xTxt As String
    On Error Resume Next
    xTxt=ActiveWindow.RangeSelection.Address
    Set xRg=Application.InputBox("Pease select range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    Set xRegEx=CreateObject("VBScript.RegExp")
    With xRegEx
        .Pattern="\D+"
        .IgnoreCase=True
        .Global=True
    End With
    xRg.NumberFormat="@"
    For Each xCell In xRg
        xCell.Value=xRegEx.Replace(xCell.Value, "")
    Next
    Set xRegEx=Nothing
End Sub