How to find numbers from a string?

I need to find numbers from a string. How does one find numbers fr开发者_StackOverflow社区om a string in VBA Excel?

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.

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

Expanding on brettdj's answer, in order to parse disjoint embedded digits into separate numbers:

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

Use the built-in VBA function Val, if the numbers are at the front end of the string:

Dim str as String
Dim lng as Long

str = "1 149 xyz"
lng = Val(str)

lng = 1149

Val Function, on MSDN

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

This a variant of brettdj's & pstraton post.

This will return a true Value and not give you the #NUM! error. And \D is shorthand for anything but digits. The rest is much like the others only with this minor fix.

Function StripChar(Txt As String) As Variant
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\D"
    StripChar = Val(.Replace(Txt, " "))
End With
End Function

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.

Alternative via Byte Array

If you assign a string to a Byte array you typically get the number equivalents of each character in pairs of the array elements. Use a loop for numeric check via the Like operator and return the joined array as string:

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

Based on @brettdj's answer using a VBScript regex ojbect with two modifications:

• The function handles variants and returns a variant. That is, to take care of a null case; and
• Uses explicit object creation, with a reference to the "Microsoft VBScript Regular Expressions 5.5" library

Function GetDigitsInVariant(inputVariant As Variant) As Variant
  ' Returns:
  '     Only the digits found in a varaint.
  ' Examples:
  '     GetDigitsInVariant(Null) => Null
  '     GetDigitsInVariant("") => ""
  '     GetDigitsInVariant(2021-/05-May/-18, Tue) => 20210518
  '     GetDigitsInVariant(2021-05-18) => 20210518
  ' Notes:
  '     If the inputVariant is null, null will be returned.
  '     If the inputVariant is "", "" will be returned.
  ' Usage:
  '     VBA IDE Menu > Tools > References ...
  '       > "Microsoft VBScript Regular Expressions 5.5" > [OK]

  ' With an explicit object reference to RegExp we can get intellisense
  ' and review the object heirarchy with the object browser
  ' (VBA IDE Menu > View > Object Browser).
  Dim regex As VBScript_RegExp_55.RegExp
  Set regex = New VBScript_RegExp_55.RegExp
  
  Dim result As Variant
  result = Null
  
  If IsNull(inputVariant) Then
    result = Null
    
  Else
    With regex
      .Global = True
      .Pattern = "[^\d]+"
      result = .Replace(inputVariant, vbNullString)
    End With
  End If
  
  GetDigitsInVariant = result
End Function

Testing:

Private Sub TestGetDigitsInVariant()
  Dim dateVariants As Variant
  dateVariants = Array(Null, "", "2021-/05-May/-18, Tue", _
          "2021-05-18", "18/05/2021", "3434 ..,sdf,sfd 444")
  
  Dim dateVariant As Variant
  For Each dateVariant In dateVariants
    Debug.Print dateVariant & ": ", , GetDigitsInVariant(dateVariant)
  Next dateVariant
  Debug.Print
End Sub

Public Function ExtractChars(strRef$) As String
'Extract characters from a string according to a range of charactors e.g'+.-1234567890'
  Dim strA$, e%, strExt$, strCnd$: strExt = "": strCnd = "+.-1234567890"
  For e = 1 To Len(strRef): strA = Mid(strRef, e, 1)
    If InStr(1, strCnd, strA) > 0 Then strExt = strExt & strA
  Next e: ExtractChars = strExt
End Function

In the immediate debug dialog:

? ExtractChars("a-5d31.78K")

-531.78