如何从Excel单元格中删除文本/字符 [英] 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

推荐答案

选择您的单元格并运行此小宏:

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

这篇关于如何从Excel单元格中删除文本/字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！