Extracting all numbers from a string

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

Hi, thanks for the effort.

But you did not follow the example I provided. The first number is 13 and not 1.

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