Extracting fields out of a block of text

[Ravi Dawar]

Hi,

I am working on a data migration project and we need to understand the source system rules. They provided us an extract of the rules which is in this form -

AND
OR
value(pi.employeeSegment) = literal(active) -- If no value is specified: [Fragment is false]
value(pi.employeeSegment) = literal(retiree) -- If no value is specified: [Fragment is false]
OR
value(pi.ACTStatus) = literal(existing) -- If no value is specified: [Fragment is false]
value(pi.ACTStatusValue) = literal(ACTV) -- If no value is specified: [Fragment is false]

I need help in extracting the following fields from this block -

1. employeeSegment
2. ACTStatus
3. ACTStatusValue

There are other 400 such blocks so I want some way in excel to give me all such parameters being used so that I can talk to the source team and get a understanding of these parameters.

*It would be a BONUS if I can somehow extract the values these parameters are being compared against. E.g. in above example, employeeSegment is being compared to active OR retiree.

Appreciate your help!

 

[Hui]

Ravi

Firstly, I draw your attention to the site rules, where the use of Urgent is not looked on favorably.
http://forum.chandoo.org/link-forums/new-users-please-read.17/
It may be urgent for you but it probably isn't for anyone else here.

We tackle problems in the order they arrive and then based on required skills

I'd suggest attaching a sample file as I am sure the data isn't quite as you have shown and it generally speeds up the solution and makes it more targetted

Please also indicate the required outcome you expect in the file

 

[Ravi Dawar]

Hui, Thank you for bringing the rules to my attention. This is my first post so please excuse me for screaming urgent on this post.
As you requested, I am attaching the excel with some sample data. The column A has the data and I created column B with the output I want.

Again, appreciate all your help!

 

[jindon]

VBA solution.
Just ignore if you don't like.

 

[Ravi Dawar]

VBA solution.
Just ignore if you don't like.

Thank you very much Jindon. This is exactly what I need. I totally don't know VBA, can you please tell me how do I scale this solution to my original sheet that has 400 rows. I added the sheet just with few rows as example.

Thank you very very much!

 

[jindon]

It should work if the data in col.A regardless of # of rows.

 

[Ravi Dawar]

Thank you very much, it worked. Would you able to give me the password to the macro you wrote in case I want to check your VBA code.

 

[jindon]

Here's the code

Sub test()
    Dim a, i As Long, m As Object, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    With Range("a2", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "value\(pi\.([^)]+)(?=\))"
            For i = 1 To UBound(a, 1)
                For Each m In .Execute(a(i, 1))
                    dic(m.submatches(0)) = Empty
                Next
                a(i, 1) = Join(dic.keys, vbLf): dic.RemoveAll
            Next
        End With
        .Columns(2).Value = a
    End With
End Sub