CODEWITHSUNDEEP
regexvba
TangoStar
TangoStar

Reputation: 551

Extracting numbers with Regular Expression

I want to extract the numbers from i.e this line:

400.0000000000000000 2520.0000000000000000 3520.0000000000000000

I have written this block of code:

Dim regxnum As New regexp
Dim searchtext As String
Dim matchxnum As MatchCollection

searchtext = "400.0000000000000000   2520.0000000000000000   3520.0000000000000000"

regxnum.Pattern = "\s*[0-9]*\.[0-9]*\s*"
Set matchxnum = regxnum.Execute(searchtext)
For Each Match In matchxnum
        MsgBox Match
Next Match

but it returns only the first number (400.0000000000000000). if I change the regular Expression to: (\s[0-9].[0-9]\s*)** then it returns the total row

how can I do this job? should I change my Regex or VBA code?

Upvotes: 0

Views: 109

Answers (2)

Tim Williams
Tim Williams

Reputation: 166196

Sub Tester()
Dim regxnum As New regexp
Dim searchtext As String
Dim matchxnum As MatchCollection, m As Match

    searchtext = "400   2520.0000000000000000   3520.0000000000000000"

    regxnum.Pattern = "\d{1,}\.{0,1}\d{0,}"
    regxnum.Global = True '**************

    Set matchxnum = regxnum.Execute(searchtext)
    For Each m In matchxnum
            Debug.Print m.Value
    Next m

End Sub

Upvotes: 2

Mike Harris
Mike Harris

Reputation: 869

Use Regex.Matches it will return a collection of all the matches.

http://msdn.microsoft.com/en-us/library/b49yw9s8.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

Upvotes: 0

Related Questions