Spliting column by different delimiters and different location of the text

[LiliLPo]

Hi, I realy need help to extract the data(comment part) above the yelow line and this needs to be dynamic. 

3 problems that I have: 

1. the comment is not always at the same place 

2. the delimitars for start and end are different. 

3. The length of the comment is different in each row

I have tried many solutions that I have found online but nothing realy works 

I will appriciate forany advice/help with this 

Thanks Lili

[LiliLPo]

Thank you very much 🙂

[ronrsnfld · Accepted solution]

You do not state how you want the output represented, so I am presenting it as concatenated text strings (concatenated with a Line Feed)

• Split on the colon
• Then split each sublist on the /
• The comments will be the last item in each sublist
• If that last item is blank ("") then delete it

let
    Source = Table.FromColumns(
        {{"PLR/93170/TC heavy rates. premier Test:BON/17423/",
         "PLR/65559/retail falt rate, 40 ft RV Test Inc FSC 253.90",
         "BAS/68050/Test Verified, closest available driver:BON/00276/1st call Member gave wrong location"
        }}, type table[Column1=text]
    ),
    #"Added Custom" = Table.AddColumn(Source, "comment", each Text.Combine(
            List.RemoveItems(
                List.Accumulate(
                    List.Transform(Text.Split([Column1],":"),
                        each Text.Split(_,"/")), 
                    {},
                    (state, current)=>
                    state & {List.Last(current)}),
                {""}),
            "#(lf)"))
in
    #"Added Custom"