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
5
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
1
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
5
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
1
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