CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
LLABS
LLABS

Reputation: 29

How to split single cell with multiple values into single

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!

Upvotes: 1

Views: 1008

Answers (2)

JPV
JPV

Reputation: 27262

For a formula-based approach you can try

=query(index(iferror(split(flatten(A3:A5&" "&split( regexreplace(B3:B5, "(\d?[A-Za-z]+)\d*", "¬$1"), "¬")), " ", 0))), "where Col2 <>''", 0)

enter image description here

Upvotes: 0

Tanaike
Tanaike

Reputation: 201388

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.

enter image description here

References:

Upvotes: 1

Related Questions