CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Christian Rudder
Christian Rudder

Reputation: 71

Splitting 1D array into 2D array with just a formula

I have a string that follows the pattern:

"course|radio-selected|party-size,course|radio-selected|party-size,..."

I'm looking to split and transpose the string vertically by , and then split horizontally by |.

After I'm looking to condense the cells to only show unique names, an average of the radio buttons selected, and the total number of people attending that course. But I can't figure out how to transpose over horizontally within the same formula.

my current formula is:

=TRANSPOSE(SPLIT(JOIN(",",'Copy of Website Submissions'!A2:A),","))

I tried turning it into an array then splitting again, but that only gives me the first entry.

Upvotes: 1

Views: 712

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

Keep in mind that we can't see what you see, and we don't know what you know, unless you show/tell us.

That said, the best I can do is take a guess. Try this:

=ArrayFormula(SPLIT(FLATTEN(SPLIT('Copy of Website Submissions'!A2:A,",")),"|"))

The key here is the FLATTEN command, which forms a single column of all results formed after the first SPLIT.

If you wind up with any blank rows, add a QUERY wrap like this:

=ArrayFormula(QUERY(SPLIT(FLATTEN(SPLIT('Copy of Website Submissions'!A2:A,",")),"|"),"Select * WHERE Col1 Is Not Null"))

If these don't work as you expect, I encourage you to share a link to a copy of your sheet with the permission set (when you create the link) to "Anyone with the link..." and "Edit."

Upvotes: 2

Related Questions