0

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?

0

1 Answer 1

0

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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