# How to split two-line rows into separate rows

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.

• 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? 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
• 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. Jan 16, 2021 at 3:48

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)))))
``````

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
• Sorry I missed that. Formulas are now not only fixed but simplified as well. Jan 16, 2021 at 9:04
• 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
• 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