How to remove text/characters from an Excel cell

问题:

I am looking for a way to remove any text from a cell and leave just numbers. So far I have found a few examples, but they either target predefined text or specialized characters.

SUBSTITUTE(A1, ".", "")
SUBSTITUTE(text, old_text, new_text, [nth_appearance])

Example, my cell can contain mixed text and numbers in any position, so how do I just leave the numbers and remove any characters...

lorem1001ipsum
ipsum01lorem
0101lorem

回答1:

Select your cells and run this small macro:

Sub qwerty()
    For Each r In Selection
        vout = ""
        v = r.Text
        n = Len(v)
        For i = 1 To n
            ch = Mid(v, i, 1)
            If ch Like "[0-9]" Then
                vout = vout & ch
            End If
        Next i
        r.Value = vout
    Next r
End Sub

回答2:

If you want to keep all numbers and period (for example, decimal numbers), add elseif to check for period. Code:

Sub qwerty() 
    For Each r In Selection
        vout = ""
        v = r.Text
        n = Len(v)
        For i = 1 To n
            ch = Mid(v, i, 1)
            If ch Like "[0-9]" Then
                vout = vout & ch
            ElseIf ch Like "." Then
                vout = vout & ch
            End If
        Next i
        r.Value = vout
    Next r
End Sub