excel - How to extract groups of numbers from a string in vba

Question

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?

Answer 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