Google-sheets – How to split two-line rows into separate rows

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregex

Editable spreadsheet here:

https://docs.google.com/spreadsheets/d/1cHf9zC00LymZgb17J746ZUoD-YRG1UvIUQQXVlovBrE/edit?usp=sharing

You can see each non-blank row contains two lines. I'm trying to find a way to split these lines into separate rows. Columns C and I do not have two lines of text, I'm fine with sticking that text in one of the split rows or duplicating the content into both rows.

I add new data daily, so ideally a script that I could run daily to split the newest content that I have added to the bottom of the sheet.

enter image description here

Best Answer

You can address your issue by using 2 similar formulas as shown in the tab I have created in your spreadsheet.

The most challenging of the two is in cell P4 where we calculate the times

=INDEX(IFERROR(REGEXREPLACE(FLATTEN(
             SPLIT(QUERY({C4:C&"@"},"where Col1 is not null",0)
                 ,CHAR(10))),"@"," ")*1))

In cell N3 we place the following formula that is replicated by pulling to the right for cells O4, Q4, R4, S4, T4, U4, V4, W4, X4, Y4.

=INDEX(IFERROR(FLATTEN( 
                 SPLIT(QUERY({A4:A},"where Col1 is not null",0)
                     ,CHAR(10)))))

enter image description here

If interested on how the formulas work, do let me know.

Functions used:

Related Topic