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

Question

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.

Answer 1

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

Answer 2

This sounds like my question, but I admit to not understanding the answers given. I had selected and copied ctrl-c about 20 lines from a Google calendar invite - a list of procedure steps. I wanted to paste this into a Google Sheet so I could add some columns surrounding it to add date, completion status, person, etc. but when I pasted with ctrl-v it all wound up on a single line, within one cell of the sheet. Not what I wanted, and my query led me here. I also found an "opposite" question here (Pasting text with line breaks into Google Sheets populates multiple cells) which sounded like the asking person had it working the way I wanted.

Anyway, based on suggestions there, I tried a right-click -> "paste special" choosing "text-only," and that still pasted all lines into a single cell, but at least these had line-breaks so it was readable. Undoing that and just trying a "Shift-Ctrl-V" finally did exactly what I wanted: pasted my 20 lines into the spreadsheet with each line on its own row. If that is what the OP was asking for, this is the way it worked for me.

Answer 3

Basic Formula

=SPLIT(A2, ";")

• Will SPLIT the string in A2 into a new column at each ;

Multi-Row Formula

=BYROW(A2:A6, LAMBDA(r, SPLIT(r, ";")))

• Will SPLIT the strings in multiple rows, A2:A6" in the example.

Answer 4

Now you can... It can be done with a subsequent copy/paste in TRANSPOSE mode