I need to clean up and split words from a mess of data into their own cells in a row. How can I acco-CodePudding

I am attempting to create documentation from an export of data that gives me a jumbled mess all in one cell that I need to clean up and extract certain bits from.

Here is an example:

[{"label":"Native Invoice","value":"native_invoice","displayOrder":0,"hidden":false,"readOnly":false},{"label":"Data Sync","value":"data_sync","displayOrder":1,"hidden":false,"readOnly":false}]

All of this is in one cell, and I need to have only the following information in their own individual rows:

Native Invoice

Data Sync

This example only has 2 values, but some that I am working on have hundreds, and it is taking far too long to manually copy and paste the values I need into their own cells.

Note: I am working in Google Sheets exclusively.

CodePudding user response：

If I'm understanding you correctly, you want to pull anything after "label": without quotes. If that's the case, and if you are open to a formula instead of a script, supposing that your raw-data block were in A1, place this in B1:

=ArrayFormula(IFERROR(QUERY(FLATTEN(REGEXREPLACE(IF(NOT(REGEXMATCH(SPLIT(REGEXREPLACE(A1,"label.:.([^"&CHAR(34)&"] )","~|$1~"),"~"),"\|")),,SPLIT(REGEXREPLACE(A1,"label.:.([^"&CHAR(34)&"] )","~|$1~"),"~")),"\|","")),"WHERE Col1 Is Not Null")))

CodePudding user response：

Here is how a custom function can look like:

function parse(txt) {
  var jsn = JSON.parse(txt);
  return [jsn[0].label, jsn[1].label];
}

Here is how it works:

You put the data into cell A1, put the formula =parse(A1) into the cell B1, and get the results in cells B1 and B2.

Update

If you want to get labels from all objects of the data, here is another variant of the function:

function get_labels(txt) {
  return JSON.parse(txt).map(x => x.label); // get 'label' from all the objects
}

It works about the same way: