How to extract groups of numbers from a string in vba

I have a string of the following shape:

RRP 90 AVE DE GAULLE 92800 PUTEAUX 0109781431-0149012126

The numbers might be seperated by other chars than hyphens (eg spaces). I know how to differentiate them afterwards with len().

I need every string of numbers to be stored seperately (in an array for example), so that I can discriminate them with len() and then use them.

I have found how to strip the characters away from the string : How to find numbers from a string?

But it doesn't suit my problem...

Could you direct me to a function or bit of code that could help me with that?

Solution 1:

This will run much faster than looping

Public Function NumericOnly(s As String) As String
    Dim s2 As String
    Dim replace_hyphen As String
    replace_hyphen = " "
    Static re As RegExp
    If re Is Nothing Then Set re = New RegExp
    re.IgnoreCase = True
    re.Global = True
    re.Pattern = "[^0-9 -]" 'includes space, if you want to exclude space "[^0-9]"
    s2 = re.Replace(s, vbNullString)
    re.Pattern = "[^0-9 ]"
    NumericOnly = re.Replace(s2, replace_hyphen)
End Function

Solution 2:

Try below code :

Function parseNum(strSearch As String) As String

   ' Dim strSearch As String
    'strSearch = "RRP 90 AVE DE GAULLE 92800 PUTEAUX 0109781431-0149012126"

    Dim i As Integer, tempVal As String
    For i = 1 To Len(strSearch)
        If IsNumeric(Mid(strSearch, i, 1)) Then
            tempVal = tempVal + Mid(strSearch, i, 1)
        End If
    Next

    parseNum = tempVal
End Function