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]

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

For 0=1,995.000 b=2,001.000 c=1,994.000 0=1,996.000 e=1,281, the result is 0, 1995, 2001, 1994, 0, 1996, 1281 For .pattern, can we add 0= as a single sign to be filter?

 

[Peter_SSs]

For 0=1,995.000 b=2,001.000 c=1,994.000 0=1,996.000 e=1,281, the result is 0, 1995, 2001, 1994, 0, 1996, 1281

The following pattern change should cope with those "0=" values

.Pattern = "(\d.*?)(?==| |$)"

Two comments though:

1. You would need to start your output more than 5 columns to the left of the original data since you have 7 output values here.

2. You may want to reconsider your Format of "#" if you want to see zero values in your results.

 

[DavidRoger]

The following pattern change should cope with those "0=" values

.Pattern = "(\d.*?)(?==| |$)"

Two comments though:

1. You would need to start your output more than 5 columns to the left of the original data since you have 7 output values here.

2. You may want to reconsider your Format of "#" if you want to see zero values in your results.

It's actually the opposite. "0=" is to be remove after split. "0=" same as "a=" to be remove after split.

 

[Rick Rothstein]

Does this code do what you want...

Sub NumbersOnly()
  Dim R As Long, C As Long, Z As Long, Data As Variant, DataOut As Variant, Nums() As String
  Data = Range("K12:K" & Range("K12").End(xlDown).Row)
  ReDim DataOut(1 To UBound(Data), 1 To 6)
  For R = 1 To UBound(Data)
    Nums = Split(Data(R, 1))
    For C = 0 To UBound(Nums)
      For Z = 1 To Len(Nums(C))
        If Mid(Nums(C), Z, 1) Like "#" Then
          DataOut(R, C + 1) = Val(Mid(Replace(Nums(C), ",", ""), Z))
          Exit For
        End If
      Next
    Next
  Next
  Range("E12:J" & 11 + UBound(DataOut)) = DataOut
End Sub

 

[DavidRoger]

Does this code do what you want...

Sub NumbersOnly()
  Dim R As Long, C As Long, Z As Long, Data As Variant, DataOut As Variant, Nums() As String
  Data = Range("K12:K" & Range("K12").End(xlDown).Row)
  ReDim DataOut(1 To UBound(Data), 1 To 6)
  For R = 1 To UBound(Data)
    Nums = Split(Data(R, 1))
    For C = 0 To UBound(Nums)
      For Z = 1 To Len(Nums(C))
        If Mid(Nums(C), Z, 1) Like "#" Then
          DataOut(R, C + 1) = Val(Mid(Replace(Nums(C), ",", ""), Z))
          Exit For
        End If
      Next
    Next
  Next
  Range("E12:J" & 11 + UBound(DataOut)) = DataOut
End Sub

Doesn't work. The result is "0=" replace the number when split.

 

[DavidRoger]

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

Hi all,

Currently I have this data,
a=1,995.000 b=2,001.000 c=1,994.000 d=1,996.000 e=1,281
using this code below to split into
1995, 2001, 1994, 1996, 1281.

Sub onerowsplit()
    Dim i As Long, r As Range
        With CreateObject("VBScript.RegExp")
         .Pattern = "([\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

To test the code, place data in K10.

However, there are non-standard data like,
0=1,995.000 b=2,001.000 c=1,994.000 d=1,996.000 e=1,281
will splited into
0, 1995, 2001, 1994, 1996, 1281.
Because there is "0=" in the data.

I would like to include code for excluding "0=" into .pattern.

Thank you.

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

 

[Rick Rothstein]

Doesn't work. The result is "0=" replace the number when split.

Sorry, I forgot to account for that. Give this code a try instead...

Sub NumbersOnly()
  Dim R As Long, C As Long, Z As Long, Data As Variant, DataOut As Variant, Nums() As String
  Data = Range("K12:K" & Range("K12").End(xlDown).Row)
  ReDim DataOut(1 To UBound(Data), 1 To 6)
  For R = 1 To UBound(Data)
    Nums = Split(Data(R, 1))
    For C = 0 To UBound(Nums)
      Nums(C) = Replace(Replace(Nums(C), "0=", "x="), ",", "")
      For Z = 1 To Len(Nums(C))
        If Mid(Nums(C), Z, 1) Like "#" Then
          DataOut(R, C + 1) = Val(Mid(Replace(Nums(C), ",", ""), Z))
          Exit For
        End If
      Next
    Next
  Next
  Range("E12:J" & 11 + UBound(DataOut)) = DataOut
End Sub

 

[Michael M]

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

Duplicate post


http://www.mrexcel.com/forum/excel-questions/802262-regex-missing-one-more-pattern-split-data.html

 

[RoryA]

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.

 

[Peter_SSs]

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.