• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Parsing string with different "separators"

C

ccarruth

Member
Have a very long text string in groups of 5 elements in this format:

data group 1,element #1:data group 1, element #2:data group 1, element #3:data group 1, element #4:data group 1,element #5;data group 2,element #1:data group 2, element #2:data group 2, element #3:data group 2, element #4:data group 2,element #5;....

I need to parse the string to:
a) count the total number data groups
b) count the total number of elements #3 where data element #5 < -60.

workbook attached..HELP! thanks guys/girls.
 

Attachments

VBA
Code: 
Sub test()
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[A-Z]+:\d+\:1:"
        MsgBox "Count of Group : " & .Execute([a4]).Count
        .Pattern = "[A-Z]+:\d+\:\d+:"
        MsgBox "Count of elements : " & .Execute([a4]).Count
    End With
End Sub
 
Thanks Jindon, ended up counting number of times the data group separator ( ; ) appeared..a bit simpler.

=LEN($L$3)-LEN(SUBSTITUTE($L$3,";",""))

I still need to actually count the total number of times where data element #5 < -60.
 
Last edited by a moderator:
Code: 
Sub test()
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[A-Z]+(:\d+){2}:[^:]+:-?\d+(\.\d+)?:(-([6-9]|\d{3})[0-9])"
        MsgBox "Count of element #3 <= 60 : " & .Execute([a4]).Count
        .Pattern = "[A-Z]+:\d+\:\d+:"
        MsgBox "Count of elements : " & .Execute([a4]).Count
    End With
End Sub
 
Last edited:
How to install your new code

  1. Copy the Excel VBA code
  2. Select the workbook in which you want to store the Excel VBA code
  3. Press Alt+F11 to open the Visual Basic Editor
  4. Choose Insert > Module
  5. Edit > Paste the macro into the module that appeared
  6. Close the VBEditor
  7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
  1. Press Alt-F8 to open the macro list
  2. Select a macro in the list
  3. Click the Run button
 
ok tried it...works great (I think).

I need the count of data element #5 when < -60 to be stored in a cell, not in a message box. Appreciate the help.

C
 
Code: 
Sub test()
    Dim a(1 To 2, 1 To 2)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[A-Z]+(:\d+){2}:[^:]+:-?\d+(\.\d+)?:(-([6-9]|\d{3})[0-9])"
        a(1, 1) = "Count of element #3 <= 60 :": a(1, 2) = .Execute([a4]).Count
        .Pattern = "[A-Z]+:\d+\:\d+:"
        a(2, 1) = "Count of elements :": a(2, 2) = .Execute([a4]).Count
    End With
    Range("C1").Resize(2, 2).Value = a
End Sub
Alter Range("C1") to suite
 
Ok..guess I am not being clear. I need to count the number of times data element #5 is less than -60 and put it, without label (nice but can't use) in a cell. There will be multiple strings, in multiple cells, so it the result can't be stored in an "anchored" cell location. Think:

A B C
row 1 customer string data elements < -60
row 2 Joe Brown ........ 31
row 3 jane doe ......... 28

Hope that clears it up..
 
Ok attached...each string is unique to each customer record (row). More records will be added so counting value needs to be automatic for any added record.
 

Attachments

See the attached
Code: 
Sub test()
    Dim r As Range, m As Object
    Application.ScreenUpdating = False
    Columns("b").Font.Bold = False
    Columns("b").Font.ColorIndex = xlAutomatic
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "([A-Z]+(:\d+){2}:[^:]+:-?\d+(\.\d+)?:)(-([6-9]|\d{3})[0-9])"
        For Each r In Range("b2", Range("b" & Rows.Count).End(xlUp))
            r(, 2).Value = .Execute(r.Value).Count
            For Each m In .Execute(r.Value)
                With r.Characters(m.firstindex + 1 + Len(m.submatches(0)), Len(m.submatches(3))).Font
                    .Bold = True: .Color = vbRed
                End With
            Next
        Next
    End With
    Application.ScreenUpdating = True
End Sub
 

Attachments

If you need to edit the code in the future, I think it is impossible for you to do.
So, better wait for someone else to come in with the formula solution...
 
You must log in or register to reply here.
Back
Top