How to extract sections from a string to create a collection

Question

I've got a string from a gallery field (galMyData.Selected.People) in the format-

lastName1, firstName1 ([email protected]) - Dept1 Dept2 Dept3  ; lastName2, firstName2 ([email protected]) - Dept1 Dept3 ; lastName3, firstName3 ([email protected]) - Dept1 Dept4  

I can split the string and create a table using-

ClearCollect(
    SelectedPeople, 
        Split(galMyData.Selected.People, " ; ") 
)

This yields a collection with all of this in a single column

lastName1, firstName1 ([email protected]) - Dept1 Dept2 Dept3  
lastName2, firstName2 ([email protected]) - Dept1 Dept3 
lastName3, firstName3 ([email protected]) - Dept2 Dept4 

I'd like to create a new collection separating this out into distinct columns. i.e.

(header)LastName FirstName FullName Email Dept1 Dept2 Dept3 Dept4
lastName firstName "lastName, firstName" [email protected] true true true false

(I don't need the header but I'm including it for illustration) I'm really not sure how to separate out the fields to create a new collection.

Any guidance would be greatly appreciated.

Answer 1

For anyone tracking this, I achieved it using this-

ForAll(
    Split(galMyData.Selected.People, " ; "), 
    Collect(reWorkedData,{
        investor: First(Split(Result, " ; " )).Result, 
        lName: First(Left(Split(Result, " ; " ), Find(", ", Result)-1)).Result, 
        fName: First(Mid(Split(Result, " ; " ),  Find(", ", Result)+2, (Find(" (", Result)-(Find(", ", Result)+2)))).Result , 
        eMail: First(Mid(Split(Result, " ; " ),  Find("(", Result)+1, (Find(")", Result)-(Find("(", Result)+1)))).Result 
        }
    )
);