In these times of distance education and online communication, many of us are spending more time making videos, perhaps for online teaching, conferences, or projects. We’re making powerpoints with sound and video, interviewing colleagues via Zoom, or stitching together clips from different sources. And there’s often pressure to to edit videos down from longer formats into something short and sweet which will keep our viewers’ attention.
I find it helpful to work from a script with timecodes, allowing me to have an overview of all the material for my videos, but split into timed sections to help me choose what to keep and what to cut to meet an overall time limit. The best way I’ve found to get a transcript for an unscripted talk is via YouTube’s automatic subtitling function. But I’ve found it tricky to export these subtitles into a format that works in Excel and in particular to get the timecodes into a usable display.
Here’s the system I ended up using; it may be of use to others.
Working with subtitles on YouTube
First you need to upload your video to YouTube and set the language for automatic speech recognition. You’ll need a YouTube account, then go to Your Videos from the bars in the top lefthand corner or access directly from studio.youtube.com
- Upload video, set language, save as private.
- Edit subtitles from the Subtitle tab in the left menu: choose DUPLICATE and EDIT, then Show timecodes. Now you can play the video in order to correct any language errors from the automatic speech recognition process, and change any breaks if you wish. Then Save draft and PUBLISH
Now you have a transcript of your video which you can use identify the main sections of interest, and the timing breakdown for each string of words as displayed in the subtitles so you know how long each part takes.
Exporting subtitles for editing in Excel
- Still from the Subtitle page on YouTube Studio, hover over the three vertical dots after EDIT to the right of the list of subtitles to see more actions. Choose the srt option from the Download option.
2. Having downloaded your srt file, convert to csv (https://gotranscript.com/subtitle-converter)
3. Now open with LibreOffice. You should see each segment with start and finish timecodes and transcript in separate columns. (If I do this directly in Excel I get a presentation where some segments are spread over two rows and this creates problems later, in LibreOffice the two lines of text appear in the same row, and therefore with the same timecode.)
4. Check for line breaks. If you have text spread over two lines for a single start and finish time, then remove the line break. Here’s the procedure
- From the Edit menu choose Search and Replace
- Search for “\n” and set a single space as replacement. (Good luck on finding backslash on your keyboard – on my Macbook Air it’s alt-shift-forward slash)
5. Now you have a clean presentation which you can paste into Excel.
Converting milliseconds to minutes and seconds in Excel
The last hurdle is getting Excel to display the timecodes in minutes and seconds rather than milliseconds so you can see at a glance start and finish times, and calculate duration (i.e., finish minus start).
- Convert milliseconds to minutes and seconds as follows
- divide by the number of seconds in 24 hours (86400000). If your start time is in cell B1, enter =B1/86400000 in cell C1, for example.
- change the display format: in Format Cells choose Number => Custom format => [h]mm:ss
2. Now you can add extra columns to show Start (ms) Start (h:mm:s), Finish (ms) Finish (h:mm:s), and a difference in milliseconds(Finish minus Start), and finally a difference (h:mm:s) which converts the previous column.
3. This gives you a spreadsheet with one line per timestamp, start and finish times in minutes and seconds, and total time for each.
So now you can use your subtitle file from YouTube in Excel to select timed extracts and calculate playing time when editing your videos.