Google Spreadsheets: Insert new rows automatically

Question

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.

Answer 1

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>.

Answer 2

You have 2 options (assuming you stay within Google Sheets)

option 1 - using formulas

use a cascade of formulas (maybe over multiple extra tabs) to calculate the following

• number of substrings in a cell
• row number of original cell
• row number of each substring
• then write a formula using the above to extract the nth substring from row x

I have done stuff like this before and it's tricky.

option 2 - scripting

Google allows you to create programs with Google Docs that can manipulate them with as much complexity as you would like.

see https://developers.google.com/apps-script/

You could write a script to loop through each row and cell and break it up into x substrings and write them as new cells to a new sheet. Very simple if you can write the code.