Google-sheets – Google Spreadsheets: Insert new rows automatically

google sheets

I have two columns, one with German texts segments (in rows) and the second one with the Spanish translations of these segments. The problem is that in many cases the segments are too long for my purpose and therefore I inserted one or more <br> marks within the segments to split the segments at this/these places, obviously I inserted the same number of breaks in both columns (the German one and the Spanish one).

I'd want now to insert automatically (I have over 5000 breaks!) new rows just below for each break.

http://i.stack.imgur.com/UCqr4.png

Best Answer

Partial answer outline

Simplified case

Source data: 1 column, | (pipe character) as separator.
Cell B1 formula:

=Transpose(Split(Join("|",A1:A2),"|"))

Formula Explanation

First, join the cells values to make one list, using a character as separator.
Second, separate the list elements at each separator.
Third, change range from m rows and n columns to n rows and m columns.

Example

Adaptation

To make the above work for the current version of the question, consider the following steps:

Assume that the first column values will be previously filled up, and it's not required to maintain the relation with the second and third columns. These values only serve as custom row IDs.
Add a second sheet.
Copy the first column.
Use the formula of the simplified case as a base for an ad-hoc formula for columns two and three. Edit the formulas to include SUBSTITUTE to add a | before each <br>.