Split multiline cell into multiple rows Google Sheets

I'm trying to split the contents in a column of cells into single rows. Below is a screenshot of the data. There are two columns whose cell content I would like to split, but keeping the end result in two columns.

In the screen shot all the content in column A could be split into multiple rows in column C (example) and the same would happen for the content in Column B where the end result will be all the content added to column D or wherever.

The number of rows to be split changes across multiple spreadsheets so we will need to use last row.

Input

Expected output

Thank you!!!

CodePudding user response：

Script

You can use the following script which will read the values from an input sheet with the name Sheet1, split the values and output them in a new sheet Result sheet that will automatically be created. You might need to adjust the sheet names to fit your needs.

function lists() {
  Logger.log("Starting script...")

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inputSheetName = "Sheet1";
  const sheet = ss.getSheetByName(inputSheetName);
  if(sheet === null) {
    Logger.log(`Sheet ${inputSheetName} must be present!`);
    return;
  }
  // create new sheet for result and delete sheet if it already exists
  const resSheetName = "Result sheet"
  let resSheet = ss.getSheetByName(resSheetName);
  if(resSheet !== null) ss.deleteSheet(resSheet);
  resSheet = ss.insertSheet(resSheetName);

  // get input values 
  const firstColumn = sheet.getRange(2, 1, sheet.getLastRow(), 1).getValues();
  const secondColumn = sheet.getRange(2, 2, sheet.getLastRow(), 1).getValues();

  // split values into one large array
  const delimiter = " "
  const resultFirstColumn = colSplit(firstColumn, delimiter);
  const resultSecondColumn = colSplit(secondColumn, delimiter);
  Logger.log(`First column splitted: ${resultFirstColumn}`);
  Logger.log(`Second column splitted: ${resultSecondColumn}`);
  // write result to output
  resSheet.getRange(1, 1).setValue("Result 1");
  resSheet.getRange(2, 1, resultFirstColumn.length, 1).setValues(resultFirstColumn);
  resSheet.getRange(1, 2).setValue("Result 2");
  resSheet.getRange(2, 2, resultSecondColumn.length, 1).setValues(resultSecondColumn);

  Logger.log("Done.")
}

function colSplit(col, delimiter){
  // we need a 2D array like this [[row1], [row2], [row3], ... ]
  return col.flatMap((cell) => cell.toString().split(delimiter).map(item => [item]))
}

Input

Output

CodePudding user response：

You can try in another sheet

=QUERY(iferror(FLATTEN(ARRAYFORMULA(split(Sheet1!A:A,char(10))))),"where Col1 is not null")