vba 如何检查Excel电子表格中的单元格是否包含数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18906581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I check if a cell in Excel spreadsheet contains number
提问by Cameel
I have a column of addresses and I have to find those which don't contain street numbers. Unfortunately, addresses have been input by various users and they do not follow the same pattern so the street type, street name, suburb are in different order and I can't use functions like LEFT, RIGHT or MID to check if particular character is a number. The column looks like this:
我有一列地址，我必须找到那些不包含街道号码的地址。不幸的是，地址是由不同的用户输入的，他们没有遵循相同的模式，所以街道类型、街道名称、郊区的顺序不同，我无法使用 LEFT、RIGHT 或 MID 等函数来检查特定字符是否为数字。该列如下所示：
10 Willsons Drive, Manhattan
Epping, 23 Wet Rd
Longsdale St, Kingsbury
11 Link Crt, Pakenham
Is there an Excel or VBA function that can tell me if cell / string contains numbers?
是否有 Excel 或 VBA 函数可以告诉我单元格/字符串是否包含数字？
回答by PatricK
Put this into a Module, then in your worksheet, may be a column next to it, put formula =HaveNumbers(A2)and check if you want it like that (True/False). You can change it to Return a String instead. This Returns TRUE / FALSE.
把它放到一个模块中，然后在你的工作表中，可能是它旁边的一列，输入公式=HaveNumbers(A2)并检查你是否想要它（真/假）。您可以将其更改为返回字符串。这将返回真/假。
Function HaveNumbers(oRng As Range) As Boolean
Dim bHaveNumbers As Boolean, i As Long
bHaveNumbers = False
For i = 1 To Len(oRng.Text)
If IsNumeric(Mid(oRng.Text, i, 1)) Then
bHaveNumbers = True
Exit For
End If
Next
HaveNumbers = bHaveNumbers
End Function
回答by Monty Wild
There isn't a single VBA function that will do what you want, but the following function should do the trick:
没有一个 VBA 函数可以满足您的要求，但是以下函数应该可以解决问题：
Public Function ContainsNumbers(Inp As String) As Boolean
Dim Strings() As String, Str As Variant
Strings = Split(Inp, " ")
For Each Str In Strings
If IsNumeric(Str) Then
ContainsNumbers = True
Exit For
End If
Next
End Function
Then put something like =ContainsNumbers(A1)in a nearby cell.
然后=ContainsNumbers(A1)在附近的单元格中放入类似的东西。
回答by Cameel
Thanks Monty. In my case, though, numbers were not always separated from words so I had to iterate over each character. I used following:
谢谢蒙蒂。但是，就我而言，数字并不总是与单词分开，因此我必须遍历每个字符。我使用了以下内容：
Function ContainsNumber(text As String)
'checks if given cell contains number
For i = 1 To Len(text)
If IsNumeric(Mid$(text, i, 1)) Then
ContainsNumber = True
Exit Function
End If
Next
ContainsNumber = False
End Function
