How to split single cell with multiple values into single

Question

Please see sample doc:

https://docs.google.com/spreadsheets/d/1Yfry2Xf26JVLeoSXpzuzT8HWZhQz4sQK5KJ5clbH-Dg/edit#gid=0

I have 2 columns of data. Col1 = id Col2 = 3 data points, but multiple rows of this data just repeats after itself in a single cell.

[id][text1 num1 num1 text2 num2 num3 text3 num3 num3]

and I need it to look like:

[id][text1][num1][num1]
[id][text2][num2][num2]
[id][text3][num3][num3]

I found this script which is able to give me the IDs in each row, but because it splits by " ", it ends up creating 2 columns and gives every value in the array an [id]. I also had a separate formula written that was helping me transpose the data so I could force it into 4 columns. Both script and formula are in the linked spreadsheet.

Possible Solution // Question

Is there a way to replace every third " " (before every [text] value) with another delimiter? Or even split on every 3rd " "?

Stuck here and would really appreciate some ideas or other methods of attack. Thanks!

Tanaike Tanaike · Accepted Answer · 2021-04-16T08:49:16

I believe your goal as follows.

You want to achieve the following situation using Google Apps Script.

From

  [id][text1 num1 num1 text2 num2 num3 text3 num3 num3]

To

  [id][text1][num1][num1]
  [id][text2][num2][num2]
  [id][text3][num3][num3]

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of your sample Google Spreadsheet. And, please put the custom formula of =SAMPLE(A3:B5) to a cell when your sample Spreadsheet is used.

const SAMPLE = values => values.reduce((ar, [a, b]) => {
  const temp = b.split(" ");
  while (temp.length > 0) {
    ar.push([a, ...temp.splice(0, 3)]);
  }
  return ar;
}, []);

Result:

When above script is run for your sample Spreadsheet, the following situation is obtained.

How to split single cell with multiple values into single

2 Answers

Sample script:

Result:

References: