vba 如何从字符串中查找数字？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7239328/
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 find numbers from a string?
提问by user905527
I need to find numbers from a string. How does one find numbers from a stringin VBA Excel?
我需要从string. 如何从stringVBA Excel 中的a中找到数字？
回答by paxdiablo
Assuming you mean you want the non-numbers stripped out, you should be able to use something like:
假设你的意思是你想要去除非数字，你应该能够使用类似的东西：
Function onlyDigits(s As String) As String
' Variables needed (remember to use "option explicit"). '
Dim retval As String ' This is the return string. '
Dim i As Integer ' Counter for character position. '
' Initialise return string to empty '
retval = ""
' For every character in input string, copy digits to '
' return string. '
For i = 1 To Len(s)
If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
retval = retval + Mid(s, i, 1)
End If
Next
' Then return the return string. '
onlyDigits = retval
End Function
Calling this with:
调用它：
Dim myStr as String
myStr = onlyDigits ("3d1fgd4g1dg5d9gdg")
MsgBox (myStr)
will give you a dialog box containing:
会给你一个对话框，其中包含：
314159
and those first two lines show how you can store it into an arbitrary string variable, to do with as you wish.
前两行显示了如何将其存储到任意字符串变量中，随心所欲。
回答by brettdj
Regular expressions are built to parse. While the syntax can take a while to pick up on this approach is very efficient, and is very flexible for handling more complex string extractions/replacements
正则表达式是为了解析而构建的。虽然语法可能需要一段时间才能掌握这种方法非常有效，并且对于处理更复杂的字符串提取/替换非常灵活
Sub Tester()
MsgBox CleanString("3d1fgd4g1dg5d9gdg")
End Sub
Function CleanString(strIn As String) As String
Dim objRegex
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "[^\d]+"
CleanString = .Replace(strIn, vbNullString)
End With
End Function
回答by pstraton
Expanding on brettdj's answer, in order to parse disjoint embedded digits into separate numbers:
扩展 brettdj 的答案，以便将不相交的嵌入数字解析为单独的数字：
Sub TestNumList()
Dim NumList As Variant 'Array
NumList = GetNums("34d1fgd43g1 dg5d999gdg2076")
Dim i As Integer
For i = LBound(NumList) To UBound(NumList)
MsgBox i + 1 & ": " & NumList(i)
Next i
End Sub
Function GetNums(ByVal strIn As String) As Variant 'Array of numeric strings
Dim RegExpObj As Object
Dim NumStr As String
Set RegExpObj = CreateObject("vbscript.regexp")
With RegExpObj
.Global = True
.Pattern = "[^\d]+"
NumStr = .Replace(strIn, " ")
End With
GetNums = Split(Trim(NumStr), " ")
End Function
回答by Rohan Moore
Use the built-in VBA function Val, if the numbers are at the front end of the string:
使用内置的 VBA 函数 Val，如果数字在字符串的前端：
Dim str as String
Dim lng as Long
str = "1 149 xyz"
lng = Val(str)
lng = 1149
lng = 1149
回答by Ste
This a variant of brettdj's & pstraton post.
这是 brettdj 和 pstraton 帖子的变体。
This will return a true Value and not give you the #NUM!error. And \Dis shorthand for anything but digits. The rest is much like the others only with this minor fix.
这将返回一个真正的值，而不是给你#NUM!错误。并且\D是除数字之外的任何东西的简写。其余的很像其他人，只有这个小修复。
Function StripChar(Txt As String) As Variant
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "\D"
StripChar = Val(.Replace(Txt, " "))
End With
End Function
回答by Top-Master
This is based on another answer, but is just reformated:
这是基于另一个答案，但只是重新调整：
Assuming you mean you want the non-numbers stripped out, you should be able to use something like:
假设你的意思是你想要去除非数字，你应该能够使用类似的东西：
'
' Skips all characters in the input string except digits
'
Function GetDigits(ByVal s As String) As String
Dim char As String
Dim i As Integer
GetDigits = ""
For i = 1 To Len(s)
char = Mid(s, i, 1)
If char >= "0" And char <= "9" Then
GetDigits = GetDigits + char
End If
Next i
End Function
Calling this with:
调用它：
Dim myStr as String
myStr = GetDigits("3d1fgd4g1dg5d9gdg")
Call MsgBox(myStr)
will give you a dialog box containing:
会给你一个对话框，其中包含：
314159
and those first two lines show how you can store it into an arbitrary string variable, to do with as you wish.
前两行显示了如何将其存储到任意字符串变量中，随心所欲。
回答by Ergin
I was looking for the answer of the same question but for a while I found my own solution and I wanted to share it for other people who will need those codes in the future. Here is another solution without function.
我一直在寻找同一问题的答案，但有一段时间我找到了自己的解决方案，我想与将来需要这些代码的其他人分享。这是另一个没有功能的解决方案。
Dim control As Boolean
Dim controlval As String
Dim resultval As String
Dim i as Integer
controlval = "A1B2C3D4"
For i = 1 To Len(controlval)
control = IsNumeric(Mid(controlval, i, 1))
If control = True Then resultval = resultval & Mid(controlval, i, 1)
Next i
resultval = 1234
结果值 = 1234
回答by T.M.
Alternative via ByteArray
通过Byte数组替代
If you assign a string to a Bytearray you typically get the number equivalents of each character in pairs of the array elements. Use a loop for numeric check via the Likeoperator and return the joined array as string:
如果将字符串分配给Byte数组，通常会以数组元素对的形式获得每个字符的等价数。通过Like运算符使用循环进行数字检查，并将连接的数组作为字符串返回：
Function Nums(s$)
Dim by() As Byte, i&, ii&
by = s: ReDim tmp(UBound(by)) ' assign string to byte array; prepare temp array
For i = 0 To UBound(by) - 1 Step 2 ' check num value in byte array (0, 2, 4 ... n-1)
If Chr(by(i)) Like "#" Then tmp(ii) = Chr(by(i)): ii = ii + 1
Next i
Nums = Trim(Join(tmp, vbNullString)) ' return string with numbers only
End Function
Example call
示例调用
Sub testByteApproach()
Dim s$: s = "a12bx99y /\:3,14159" ' [1] define original string
Debug.Print s & " => " & Nums(s) ' [2] display original string and result
End Sub
would display the original string and the result string in the immediate window:
将在立即窗口中显示原始字符串和结果字符串：
a12bx99y /\:3,14159 => 1299314159
