2

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

Improve this question
3
  • 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.
    – Sean Everett
    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

Reset to default
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

=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:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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