Answered

Split nested concatenated string values into 2 column collection

(0) Share

Report

Posted on by ES-11101448-0

Hello all,

I am building an app for a gymnastics tournament where participants for a certain exercise are listed with their sequential number. For example:

Exercise A has 5 participants with their unique ID number concatenated with their sequential number (delimiter "-"), concatenated into one text string using delimiter ";". So it is a nested concatenated list of participants and their sequence number: [1-1;2-5;3-2;4-4;5-3], meaning participantID 1 is first, participantID 2 is 5th and so on.

I want to split these strings into a two column collection like so:

ID Sequence

1 1

2 5

3 2

4 4

5 3

Anybody have an idea how to do this?

Categories:

Using Apps Using Formulas

All responses (6)

Answers (2)

ES-11101448-0 on at

Like (0)

Report

Re: Split nested concatenated string values into 2 column collection

You're right, I got it working with a gallery and a collection just fine. Thanks for the help.
LaurensM 12,510 Super User 2025 Season 1 on at

Like (0)

Report

Re: Split nested concatenated string values into 2 column collection

@eschoo,

I indeed think displaying the values as a table within an editable gallery would be beneficial UI/UX wise. I would not let the users view or edit the concatenated string directly as text.

For more specific advice, we may need more information about your app or the set-up. Is there a specific reason why you think that a collection and gallery may not be the most optimal approach?
ES-11101448-0 on at

Like (0)

Report

Re: Split nested concatenated string values into 2 column collection

Great! My apologies to all for the confusion about the square brackets. Without the Mid code it works perfectly.

Do you have a better way to handle the editing of sequential numbers via a gallery using this colParticipants? Or is this the most convenient way?
ES-11101448-0 on at

Like (0)

Report

Re: Split nested concatenated string values into 2 column collection

Wow thanks! That did the trick. My string doesn't have the brackets in the Sharepoint List entry, so using a second split function would also work. Would you mind showing me how this would work?

So my Sharepoint list entry is: 1-1;2-5;3-2;4-4;5-3

I want to show the participants and their sequential number in a gallery where the sequential numbers can be edited and saved. I am now doing this via this myCollection. Is there an easier way to do this as well?
Verified answer

LaurensM 12,510 Super User 2025 Season 1 on at

Like (2)

Report
Re: Split nested concatenated string values into 2 column collection

Hi @eschoo,

@CarlosFigueira's code will do the trick. Wouldn't have thought of doing it that way, thanks for sharing Carlos! 😊

Just adding the code below as an example of how to achieve a similar result solely using PowerFx:

ClearCollect( colParticipants, ForAll( Split( //Remove square brackets Mid( "[1-1;2-5;3-2;4-4;5-3]", 2, Len("[1-1;2-5;3-2;4-4;5-3]") - 2 ), ";" ), With( {wSplitValue: Split(ThisRecord.Value, "-")}, {ID: First(wSplitValue).Value, Sequence: Last(wSplitValue).Value} ) ) )

I hope this helps!
Verified answer

CarlosFigueira on at

Like (3)

Report
Re: Split nested concatenated string values into 2 column collection

You can use a combination of a few functions to accomplish what you need:

Split to break down the string in the parts separated by ';'

ForAll to loop through the parts from the previous step

Match to extract the two numeric parts, split by '-' (you could also accomplish it by using Split again, but you would need to remove the '[' and ']' at the beginning and end)

Collect and Clear to save the parts into a collection.

Clear(myCollection); ForAll( Split("[1-1;2-5;3-2;4-4;5-3]", ";") As splitResult, With( { match: Match(splitResult.Value, "(?<id>\d+)\-(?<seq>\d+)") }, If( Not IsBlank(match), Collect(myCollection, { ID: match.id, Sequence: match.seq }) ) ) )

Hope this helps!