Editable spreadsheet here:


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

  • 1
    While I can imagine ways of doing this, I don't know of any that will do it AND retain your links for the team names, "Summary," and "BoxScore." Do you need those?
    – Erik Tyler
    Jan 14, 2021 at 11:34
  • Thanks, Erik, no, I don't need to retain the links. I would have pasted the data as plain text but it wouldn't paste correctly. Jan 15, 2021 at 15:07
  • 2
    Looks like @marikamitsos has provided you with a working solution. If that is what you expected, be sure to mark that answer as "Best Answer" to signal to others that this issue has been resolved.
    – Erik Tyler
    Jan 16, 2021 at 3:48

1 Answer 1


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

             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.

                 SPLIT(QUERY({A4:A},"where Col1 is not null",0)

enter image description here

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

Functions used:

  • This looks great except for one issue, where instead of scores there are hyphens (postponed games). See row 22, where it grabs the scores from the following game instead of grabbing the hyphens from the current game. Thanks so much for your help here, I had no idea Google Sheets could handle this without a script. Jan 16, 2021 at 4:23
  • 1
    Sorry I missed that. Formulas are now not only fixed but simplified as well. Jan 16, 2021 at 9:04
  • 1
    Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. Jan 16, 2021 at 9:05
  • 1
    Wow, amazing work, thanks so much! Are tips allowed on this site? Jan 16, 2021 at 16:46
  • I only wish sometimes. Glad I could help :) Jan 17, 2021 at 17:37

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.