Link to home
Create AccountLog in
Avatar of Saqib Husain
Saqib HusainFlag for Pakistan

asked on

Extracting all numbers from a string

I need to extract all instances of numbers in a given string. I have posted a few sample strings in the attached excel file with the expected results for the first string next to a command button.

Can someone provide VBA code which will extract the numbers as listed and also provide the location of each number within the string.
NumericExtractor.xlsm
Avatar of therealmongoose
therealmongoose
Flag of United Kingdom of Great Britain and Northern Ireland image

Option Explicit

Sub TestFunction()

    Dim strInString As String
    Dim lngNumbers() As Long
    Dim lngElement As Long
   
    strInString = "        Me.Table13LayoutPanel1.Controls.Add(Me.Label2,1 11, 0)"
   
    lngNumbers = fncNumbersFromString(strInString)
   
    For lngElement = 0 To UBound(lngNumbers, 2)
   
        Debug.Print "Number " & lngNumbers(0, lngElement) & " was found at position " & lngNumbers(1, lngElement)
       
    Next lngElement
       

End Sub


Function fncNumbersFromString(strString As String) As Long()

    Dim lngNumberArray() As Long
    Dim lngCharPos As Long
   
    ReDim lngNumberArray(1, 0)
   
    For lngCharPos = 1 To Len(strString)
   
        If IsNumeric(Mid(strString, lngCharPos, 1)) Then
            lngNumberArray(0, UBound(lngNumberArray(), 2)) = CLng(Mid(strString, lngCharPos, 1)) ' the number
            lngNumberArray(1, UBound(lngNumberArray(), 2)) = lngCharPos                          'the position
            ReDim Preserve lngNumberArray(1, UBound(lngNumberArray, 2) + 1)
        End If
       
    Next lngCharPos
   
    If UBound(lngNumberArray, 2) > 0 Then
        'At least 1 number was found, remove empty array
        ReDim Preserve lngNumberArray(1, UBound(lngNumberArray, 2) - 1)
    Else
        'no numbers were found in string, no need to redimension
        ' do nothing
    End If
   
    fncNumbersFromString = lngNumberArray
   
End Function
Avatar of Saqib Husain

ASKER

Hi, thanks for the effort.

But you did not follow the example I provided. The first number is 13 and not 1.
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hello Saqib
Hi, what a pleasant surprise.....long time.

... I made it so the result goes on a separate sheet Result. 
No problem. I can handle that.
In your sample the first item 13 is at position 17 not 18 !!!
Thanks for the correction

Here is the code

Let me know
All good
Great Saqib Glad I could help. See u
Gowflow