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.


  • you can use app-script to add new row dynamically.. Jun 9, 2015 at 11:36
  • 1
    Do you really want to keep <br> in there, as the screenshot shows?
    – user79865
    Jun 9, 2015 at 17:51

2 Answers 2


Partial answer outline

Simplified case

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


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.



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


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.

  • Did you already considered the use of array formulas with split and join functions? Jun 9, 2015 at 12:16
  • I don't see how that would work with the question as-is. "Split" works on single character delimiters, not substrings (and strips the delimiters) Regextract only returns the first match in a string. also "Arrayformula" needs existing blank cells to put its results in and cannot automatically insert a varying number to make room. The root cause of the difficult is that each row maps to an unknown and variable number of results rows, which is not the best use-case for spreadsheets. Jun 9, 2015 at 12:44
  • I think that as part of the answer we could suggest some changes in the overall procedure, like changing the separation marker. Later I will add more details about the use of the functions mentioned in my previous comment. Jun 9, 2015 at 12:51
  • Could the downvoter add some specific reason why the answer is "not helpful" please ? I have personally used BOTH suggestions on multiple spreadsheet platforms, including Google, and they work. Bear in mind the OP's original un-edited question asked for general advise on how to solve the problem (as opposed to a fully completed zero-effort solution) . If you are looking for a complete zero-effort solution please be explicit about that requirement and perhaps open a new question. Thanks. Jun 11, 2015 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.