Extract multiple numbers from a text string???

Hi there, basically i have a text string with text and multiple numbers at different character positions in a cell. I need to extract these numbers only, doesnt really matter in what format as long as i get ALL the numbers in another cell. A sample of a typical cell is below:



363 - which station poster232 - cab tube179 - inter car barrier188 - control key replaced893 CAB LIGHT TUBE340 LIGHT TUBE111 LIGHT TUBE

i want all the numbers within this text string.

I have previously used the following custom formula to extract the numbers but that only works to extract the first 15 numbers and then for the remaining numbers it just returns 0's, although they are the exact amount of zeros as are the exact amount of numbers left in the string.

Function ExtractNumber(rCell As Range, _
     Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As Double
 
Dim iCount As Integer, i As Integer, iLoop As Integer
 
    Dim sText As String, strNeg As String, strDec As String
 
    Dim lNum As String
 
    Dim vVal, vVal2
 
    sText = rCell
 
    If Take_decimal = True And Take_negative = True Then
 
        strNeg = "-"
 
        strDec = "."
 
    ElseIf Take_decimal = True And Take_negative = False Then
 
        strNeg = vbNullString
 
        strDec = "."
 
    ElseIf Take_decimal = False And Take_negative = True Then
 
        strNeg = "-"
 
        strDec = vbNullString
 
    End If
 
    iLoop = Len(sText)
 
            For iCount = iLoop To 1 Step -1
 
            vVal = Mid(sText, iCount, 1)
 
                If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
 
                    i = i + 1
 
                    lNum = Mid(sText, iCount, 1) & lNum
 
                        If IsNumeric(lNum) Then
 
                            If CDbl(lNum) < 0 Then Exit For
 
                        Else
 
                          lNum = Replace(lNum, Left(lNum, 1), "", , 1)
 
                        End If
 
                End If
 
                If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
 
            Next iCount
 
    ExtractNumber = CDbl(lNum)
 
End Function

The result that i get with this formula is 363232179188893000000

this is not what i want, i want all the numbers.


Anyone any help please?? what am i doing wrong here?

Thanks

Change the function to return a string instead of a double

Function ExtractNumber(rCell As Range, _
     Optional Take_decimal As Boolean, Optional Take_negative As Boolean) As String
     ......
    ExtractNumber = lNum
End Function

Another way:

Function GetNums(sInp As String) As String
    Dim i As Long
    
    For i = 1 To Len(sInp)
        Select Case Mid(sInp, i, 1)
            Case "0" To "9"
                GetNums = GetNums & Mid(sInp, i, 1)
        End Select
    Next i
End Function