How to separate YouTube chapters text to columns and rows in a spreadsheet?

Question

Some YouTube videos have a list of chapters in their description. How can these chapter descriptions be split or separated into columns and rows? Usually the format in the description is as follows:
Time Description
00:00 Intro before you start
01:30 First part
02:45 Second part
03:50 Third part
05:15 End and conclusion

If this text is copied then pasted into a spreadsheet then the text will be in a single cell with the following format:
00:00 Intro before you start 01:30 First part 02:45 Second part 03:50 Third part
05:15 End and conclusion

Google Sheets has a split function and a split text to columns option but I can't figure out what a proper delimiter is for this. I'm guessing the first space following any integer could be the delimiter. Can this be done with a regular expression?

Answer 1

Source Data: Pasted in a Single Cell

---

Assuming all your data is in a single cell A1, the following formula will work regardless if the string includes line breaks CHAR(10).

={"TIME","TITLE";ARRAYFORMULA(SPLIT(
     TRANSPOSE(SPLIT(
        REGEXREPLACE(REGEXREPLACE(
           SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
              A1, " -", "-" ),"- ", "-"),char(10), " "),
           "(Chapters\s+)",""),"(\d+:)", "🙂$1"),
        "🙂")),
     "-"))}

   

Source Data: One Chapter per Row

---

Assuming your source data is in a range of rows that starts in Cell A1, the following formula will work on that range.

NOTE: In my formula the first row in my range is actually A2. Even though my data starts in A1 I don't want to process the row that contains only the word "Chapters".

=BYROW(A2:A12, LAMBDA(x, SPLIT(
    SUBSTITUTE(SUBSTITUTE(x," -","-"),"- ","-"),
    "-")))

   

Images

---

Single Cell w/ line breaks
 
 
Single Cell w/out breaks
 
 
One Chapter per Row