Google-sheets – way on google sheets to paste text and automaticly create new rows with every line break or etc

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

Is there away on google sheets to paste text and automaticly create new rows with every line break or ect?

I will paste a lot of text by this format in the example below (700 lines in total):

wizard_setup_process_title;setup_wizard;1;1;Setup process;;true;
wizard_setup_process_description;setup_wizard;2;1;In just a few steps you will be setup and ready to go!;;true;
wizard_optimization_settings;setup_wizard;3;1;Optimisation settings;;true;
wizard_alt_tag_settings;setup_wizard;4;1;ALT Tag settings;;true;
wizard_payment_method;setup_wizard;5;1;Payment method;;true;

For now, I can only format columns by doing data->split text to columns, the semicolan at the end of a string seperates column, two semicolons leaves a column empty.

Is there any similiar way to split text to rows or ect?

Any help will be appreciated.

Best Answer

There is no built-in function to automatically split pasted data into row but you can use a on edit simple or installable trigger from Google Apps Script.

The edit event object includes a range property that can be used to grab the pasted values. Then you could use JavaScript to split those values into rows and then use the Google Apps Script Spreadsheet Service to replace the pasted data by the splitted data.

Resources

https://developers.google.com/apps-script/guides/sheets

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Best Answer

Related Solutions

Google Sheets – Pasting Text with Line Breaks Populates Multiple Cells

Google Sheets – How to Split Multi-Line Cells into New Rows

Code

Explanation

Screenshot

Example

Related Topic