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.

 

[VoG]

Cross posted Regex: Matching pattern to split a cell data

 

[DavidRoger]

Cross posted Regex: Matching pattern to split a cell data

Thank you.

 

[Peter_SSs]

Try

.Pattern = "\d+\,?\d*\.?\d*"

If not, please provide a couple of examples where it fails together with the expected results.

 

[Peter_SSs]

Actually have a different pattern here, and a slightly changed code suggestion to save executing the RexEx pattern search multiple times for the one piece of data.
My pattern this time is based on your sample data showing that each "number" is followed by either a space or the end of the string.
I've also removed the If .Test() .. line as it is not required with this structure.

Sub onerowsplit()
  Dim i As Long, r As Range, ary As Object
  
  With CreateObject("VBScript.RegExp")
    .Pattern = "(\d.*?)(?= |$)"
    .Global = True
    For Each r In Range(Range("k10").Value2)
        Set ary = .Execute(r.Value)
        For i = 0 To ary.Count - 1
          r(, i - 5).Value = Format(ary(i), "#")
        Next i
    Next r
  End With
End Sub

 

[DavidRoger]

Actually have a different pattern here, and a slightly changed code suggestion to save executing the RexEx pattern search multiple times for the one piece of data.
My pattern this time is based on your sample data showing that each "number" is followed by either a space or the end of the string.
I've also removed the If .Test() .. line as it is not required with this structure.

Sub onerowsplit()
  Dim i As Long, r As Range, ary As Object
  
  With CreateObject("VBScript.RegExp")
    .Pattern = "(\d.*?)(?= |$)"
    .Global = True
    For Each r In Range(Range("k10").Value2)
        Set ary = .Execute(r.Value)
        For i = 0 To ary.Count - 1
          r(, i - 5).Value = Format(ary(i), "#")
        Next i
    Next r
  End With
End Sub

Hi Peter,

Let me study first. Probably I get back to you tomorrow.

 

[DavidRoger]

Actually have a different pattern here, and a slightly changed code suggestion to save executing the RexEx pattern search multiple times for the one piece of data.
My pattern this time is based on your sample data showing that each "number" is followed by either a space or the end of the string.
I've also removed the If .Test() .. line as it is not required with this structure.

Sub onerowsplit()
  Dim i As Long, r As Range, ary As Object
  
  With CreateObject("VBScript.RegExp")
    .Pattern = "(\d.*?)(?= |$)"
    .Global = True
    For Each r In Range(Range("k10").Value2)
        Set ary = .Execute(r.Value)
        For i = 0 To ary.Count - 1
          r(, i - 5).Value = Format(ary(i), "#")
        Next i
    Next r
  End With
End Sub

I have tested it and explore it. Good one.

Thank you.

If you are free, please check the attachment through this link. Regex: Matching pattern to split a cell data [SOLVED]. You might want to have a look at AB33 version of pattern.

 

[Peter_SSs]

I have tested it and explore it. Good one.

Thank you.

Glad to help.

If you are free, please check the attachment through this link. Regex: Matching pattern to split a cell data [SOLVED]. You might want to have a look at AB33 version of pattern.

As a general rule I choose not to download files from other sites. In any case that link just gives me a security warning stating that the site contains malware - confirming my reluctance to do such downloads.

 

[DavidRoger]

I notice another problem when apply some of the data. Some of the data are in this state, 0=1,995.000 b=2,001.000 c=1,994.000 0=1,996.000 e=1,281 "0=" is zero equal. As a result, it cant split probably.

 

[Peter_SSs]

I notice another problem when apply some of the data. Some of the data are in this state, 0=1,995.000 b=2,001.000 c=1,994.000 0=1,996.000 e=1,281 "0=" is zero equal. As a result, it cant split probably.

I'm not quite sure where that data sample ends or what the expected results are?