Identifying multiple values from two reoccurring conditions in a string

[Rav_Singh]

SOS, hoping someone can provide assistance to this challenge I have failed to overcome.


I require to identify specific text nestled between two text values. From the example below I require to identify text values between text range '_taxonomy_' and text range ' " ', both highlighted in bold.


From the below example the output I require is to extract into other cells;


Cell 1 = dir_org_practice_area
Cell 2 = dir_loc_office
Cell 3 = ppl_job_title


The challenge is that these ranges occur multiple times in a string


query=&rm_within_search=&sort_index_dir_sortby=rel&sort_order_dir_sortby=ascending&rm_taxonomy_dir_org_practice_area=Hong+kong+%28litigation%29&rm_taxonomy_dir_loc_office=Hong+kong&rm_taxonomy_ppl_job_title=Secretary&resultsize=100


Mumps kindly provided the formula below (for a similar example) to identify specific text when the range is used only once in a string, however I now require a formula that can identify and extract text from a reoccurring range in a string.


=MID(A1,FIND("_taxonomy_",A1,1)+10,LEN(A1)-FIND("=",A1,FIND("_taxonomy_",A1,1))-10-1) Mumps




An Olympic gold medal nomination if someone can crack this.


Any assistance would be greatly appreciated.

Rav

 

[lrobbo314]

This can be done with Power Query.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Rename = Table.RenameColumns(Source,{{"Column1", "Raw"}}),
    Split = Table.ExpandListColumn(Table.TransformColumns(Rename, {{"Raw", Splitter.SplitTextByDelimiter("_taxonomy_", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Raw"),
    Remove = Table.Skip(Split,1),
    Extract = Table.TransformColumns(Remove, {{"Raw", each Text.BeforeDelimiter(_, "="), type text}})
in
    Extract

 

[lrobbo314]

Or a VBA solution.

Sub Extract()
Dim s As String: s = Range("A1").Value
Dim SP() As String: SP = Split(s, "_taxonomy_")
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")

For i = 1 To UBound(SP)
    AL.Add Left(SP(i), InStr(SP(i), "=") - 1)
Next i

Range("C1").Resize(AL.Count, 1).Value = Application.Transpose(AL.ToArray)

End Sub

 

[sandy666]

                BMMMB:                            
               DP   :BO                           
               Ms     HM                          
               RR  .   ZS                         
               :B  ..   B                         
                B: ...  M:                        
                B1 .... O                         
                Mx ... ;r                         
                B  .. :s                          
               Bi ...rO77xxuxLi:                  
             7B;  ..7:  . ..,:7u0PL               
            OZ     .             ,sBP             
           SW   r7sr7i  .,;;;.  .   XM            
           B,  Ei  .,3R01L:,......   BF           
           P7 ,M ..   LS    ......  .Mr           
            P  Wr ,, .   .......   sMi            
            ;F  R; .:.. .....   :xBs              
            X.   B1:..      .iJPU:iB              
           .c . iR,JS7::;;rsrv;    MB             
           :J   :M  .::;:,..      .B:             
            B,   BZ.            .sR,              
            ,B7 R;;E17:....::rrFM,                
              EWB;  ;c131uJc7;. B.                
                .S3:           cM                 
                  ,ivxi::,::;r7v                  
                     :i7vcri:,

 

[lrobbo314]

Assuming that you have more than just the 1 string to parse, this modified code will correctly extract each row.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Rename = Table.RenameColumns(Source,{{"Column1", "Raw"}}),
    Split = Table.SplitColumn(Rename, "Raw", Splitter.SplitTextByDelimiter("_taxonomy_", QuoteStyle.Csv), {"Raw.1", "Raw.2", "Raw.3", "Raw.4"}),
    Remove = Table.RemoveColumns(Split,{"Raw.1"}),
    Exctract = Table.TransformColumns(Remove, {{"Raw.2", each Text.BeforeDelimiter(_, "="), type text}, {"Raw.3", each Text.BeforeDelimiter(_, "="), type text}, {"Raw.4", each Text.BeforeDelimiter(_, "="), type text}})
in
    Exctract

 

[Rav_Singh]

Firstly many thanks Irobbo314, I really appreciate you sharing your knowledge and expertise on my problem.


Unfortunately power query is not enabled on my work Excel for some reason.


When I run the VBA code for a single record, then your code is perfect however when I try and run the code for 2000 records in column A then I encounter the error message 'Run-time error ‘13'. I presume the code does not cater for multiple rows of data to review? Is it even possible for the extracted text to be entered in the next neighbouring column cells rather than below?


Again many thanks for your input, any further assistance will be greatly received.

 

[lrobbo314]

Try this code

Sub Extract()
Application.ScreenUpdating = False
Dim AR() As Variant: AR = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim SP() As String
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim tmp As String
Dim r As Range

For i = LBound(AR) To UBound(AR)
    SP = Split(AR(i, 1), "_taxonomy_")
    For j = 1 To UBound(SP)
        tmp = tmp & Left(SP(j), InStr(SP(j), "=") - 1) & "@"
    Next j
    AL.Add Left(tmp, Len(tmp) - 1)
    tmp = vbNullString
Next i

Set r = Range("B2").Resize(AL.Count, 1)

With r
    .Value = Application.Transpose(AL.ToArray)
    .TextToColumns DataType:=xlDelimited, Other:=True, OtherChar:="@"
End With

Application.ScreenUpdating = True
End Sub

 

[Rav_Singh]

Dear lrobbo314,

This was fantastic. I should have mentioned that certain rows would be blank or not contain the specified range values but I have a work-around for that.

Take a bow, your help was greatly appreciated.

Rav