Regex: Missing one more pattern for split 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

Please put the data in column K.

 

[DavidRoger]

Re: regex: include split "0=" like "a=" into .pattern

Change the pattern to:

         .Pattern = "([\d.,]+(?!=))"

Note: I have merged your two threads together since they are on the same question. Please do not post duplicates.

Ok Thank you

 

[DavidRoger]

Re: regex: include split "0=" like "a=" into .pattern

I'm still confused about what you really want. Instead of trying to describe in words what you want using terms like "split" and "filter", it would help greatly if you could give, say 5 or 10 varied samples of data and the results you WANT from that data. My signature block below has suggestions for how you can provide a small screen shot which might also help clarify required layout.

I am sorry for the cause. Please refer to post #16.

Here are one example below:

When apply macro result should be 1995, 2001, 1994, 1996, 1281.

Because some data instead of "a=" has "0=" and
the result after split is 0, 1995, 2001, 1994, 1996, 1281.

All result should in this state: 1995, 2001, 1994, 1996, 1281 based either one of the data above.

Hope is clearer now.

 

[Peter_SSs]

Re: regex: include split "0=" like "a=" into .pattern

Here are one example below:

Hmm, that's hardly 5 or 10.

Never-the-less, from what we have seen of your data, I suspect Rory's Pattern will do what you want.