vba 如何在vba中检查字符串是否仅包含数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23177442/
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 to check if a string contains only numeric numbers in vba
提问by TitanTheYaphet
I want to parse out the year info from a string like this one
我想从这样的字符串中解析出年份信息
$8995 Apr 18 2008 Honda Civic Hybrid $8995 (Orem) pic map cars & trucks - by owner
8995 美元 2008 年 4 月 18 日本田思域混合动力车 8995 美元（Orem）图片地图汽车和卡车 - 所有者提供
Since I retrieve this string online, sometimes the year element is not at the same place. The way I do it is to split the string by space using split function, then check if each node of the array contains only numeric digits.
由于我在线检索此字符串，因此有时年份元素不在同一位置。我这样做的方法是使用 split 函数按空格拆分字符串，然后检查数组的每个节点是否仅包含数字。
However when i use the function IsNumeric, it also returns "$8995" node as true as well.
但是，当我使用函数 IsNumeric 时，它也会返回“$8995”节点为真。
What is a good way to check if a string contains only numbers, no "$", no ".", not anything else?
检查字符串是否只包含数字，没有“$”，没有“.”，没有其他任何东西的好方法是什么？
Or in my situation, is there a better way to retrieve the year information?
或者在我的情况下，有没有更好的方法来检索年份信息？
Thanks.
谢谢。
回答by user2426679
This can be accomplished as a single line of code, using the Likeoperator
这可以作为一行代码完成，使用Like运算符
Function StringIsDigits(ByVal s As String) As Boolean
StringIsDigits = Len(s) And (s Like String(Len(s), "#"))
End Function
回答by Ron Rosenfeld
Will it be the case that all the strings with "years" will have substrings that look like dates? If that is the case, you could just cycle through the string looking for the first group of three that looks like a date, extracting the year from that:
所有带有“years”的字符串都会有看起来像日期的子字符串吗？如果是这种情况，您可以在字符串中循环查找看起来像日期的第一组三个，从中提取年份：
Option Explicit
Function FindYear(S As String) As Long
Dim SS As Variant
Dim sDate As String
Dim I As Long, J As Long
SS = Split(S, " ")
For I = 0 To UBound(SS) - 2
sDate = ""
For J = 0 To 2
sDate = " " & sDate & " " & SS(I + J)
Next J
sDate = Trim(sDate)
If IsDate(sDate) Then
FindYear = Year(sDate)
Exit Function
End If
Next I
End Function
回答by David Zemens
WIthout using Regular Expressions or some very complicated logic, it's going to be difficult to be perfect.
如果不使用正则表达式或一些非常复杂的逻辑，就很难做到完美。
This code will return the pure numeric substrings, but in the case of your example it will return "18" and "2008". You could obviously try to add some more logic to disallow "18" (but allow "13" or "09", etc., but like I said that starts getting complicated. I am happy to help with that, but not knowing exactly what you want, I think it's best to leave that up to you for now.
此代码将返回纯数字子字符串，但在您的示例中，它将返回“18”和“2008”。您显然可以尝试添加更多逻辑来禁止“18”（但允许“13”或“09”等，但就像我说的那样开始变得复杂。我很乐意提供帮助，但不知道究竟是什么你想要，我认为最好暂时把它留给你。
Const str$ = "95 Apr 18 2008 Honda Civic Hybrid 95 (Orem) pic map cars & trucks - by owner"
Option Explicit
Sub FindNumericValues()
Dim var() As String
Dim numbers As Variant
var = Split(str, " ")
numbers = GetNumerics(var)
MsgBox Join(numbers, ",")
End Sub
Function GetNumerics(words() As String) As Variant
Dim tmp() As Variant
Dim i As Integer
Dim n As Integer
Dim word As Variant
Dim bNumeric As Boolean
For Each word In words
n = 0
bNumeric = True
Do While n < Len(word)
n = n + 1
If Not IsNumeric(Mid(word, n, 1)) Then
bNumeric = False
Exit Do
End If
Loop
If bNumeric Then
ReDim Preserve tmp(i)
tmp(i) = word
i = i + 1
End If
Next
GetNumerics = tmp
End Function
回答by Sam
You could parse the year out using RegEx:
您可以使用 RegEx 解析年份：
Public Function GetYear(someText As String) As Integer
With CreateObject("VBScript.RegExp")
.Global = False
.MultiLine = False
.IgnoreCase = True
.Pattern = " [\d]{4} "
If .Test(testString) Then
GetYear = CInt(.Execute(testString)(0))
Else
GetYear = 9999
End If
End With
End Function
Example code:
示例代码：
Public Const testString As String = "95 Apr 18 2008 Honda Civic Hybrid 95 (Orem) pic map cars & trucks - by owner "
Public Function GetYear(someText As String) As Integer
With CreateObject("VBScript.RegExp")
.Global = False
.MultiLine = False
.IgnoreCase = True
.Pattern = " [\d]{4} "
If .Test(testString) Then
GetYear = CInt(.Execute(testString)(0))
Else
GetYear = 9999
End If
End With
End Function
Sub Foo()
Debug.Print GetYear(testString) '// "2008"
End Sub
