How to find numbers from a string?

Question

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

Answer 1

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.

Answer 2

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