如何从Excel单元格中删除文本/字符

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

2 个解决方案

#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:

如果要保留所有数字和句点(例如,十进制数字),请添加elseif以检查句点。码:

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

#1