# How to split two-line rows into separate rows

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.

• 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 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. – Sean Everett Jan 15 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. – Erik Tyler Jan 16 at 3:48

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

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. – Sean Everett Jan 16 at 4:23
• Sorry I missed that. Formulas are now not only fixed but simplified as well. – marikamitsos Jan 16 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. – marikamitsos Jan 16 at 9:05
• Wow, amazing work, thanks so much! Are tips allowed on this site? – Sean Everett Jan 16 at 16:46
• I only wish sometimes. Glad I could help :) – marikamitsos Jan 17 at 17:37