如何从字符串中找到数字?

I need to find numbers from a string. How does one find numbers from a string in VBA Excel?

我需要从字符串中找到数字。如何从VBA Excel中的字符串中找到数字?

3 个解决方案

#1

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:

将为您提供一个对话框，其中包含:

and those first two lines show how you can store it into an arbitrary string variable, to do with as you wish.

前两行显示了如何将它存储到任意的字符串变量中，如您所愿。

#2

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

#3

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

#1