Regex: Matching pattern to split a cell data

[DavidRoger]

Hi all,

Basically I want to split data into each cell just the number without alphabet and special character.

The data I want to split is:
a=1,995.000 b=2,001.000 c=1,994.000 d=1,996.000 e=1,281

Currently I have a set of code. New to regex, I need some help with the pattern.

The code work well with the above data. But cant split if the data doesn't have "," or ".".
ie, e=999. Search and trying a lot, I guess if the .pattern can be fixed then the code is complete.

The code is as below:

Sub onerowsplit()
    Dim i As Long, r As Range
        With CreateObject("VBScript.RegExp")
             .Pattern = "\d+[\,\.](\d+)?"
             .Global = True
          For Each r In Range(Range("k10").Value2)
             If .test(r.Value) Then
                For i = 0 To .Execute(r.Value).Count - 1
                    r(, i - 5).Value = Format(.Execute(r.Value)(i), "#")
                Next
             End If
          Next
        End With
End Sub

By Ab33

Please put the data in column K.

If you want to see attachment, it below.


http://www.mrexcel.com/forum/excel-q...ml#post3922941