When I use a YouTube video - such as an interview - in the course of my work, I often grab the transcript. This enables me to accurately cite what was said. All good so far.
The trick is that (a) if we access the transcript (i.e. click the view transcript link in the video description to pop it out alongside the video), and (b) copy it, what we get is a text string of the time stamp, a hard return, and then the text. Rinse, repeat.
I can paste my copied transcript into a table in Word. However, a one hour interview becomes a long document with less than optimal searchability and filtering. For example, say I am looking for the word "19" in the transcript. While, I can search for "19" in Word, that is going to pick up every 19 second time stamp, every 19 minute time stamp, and every other time 19 is mentioned... it can be a long list of hits.
For better quality searchability and filtering, I prefer to use Excel. But when I copy my transcript into Excel, I get alternate rows of the time stamp, then the text. I need a way to separate these two without disconnecting the time stamp from the text, in case I want to quote from the interview.
There is an easy work around: copy the data once beginning in B2, then again into C2 (we leave A2 blank for now). Now click on the single cell B2, and using the File | Delete function, click the "Delete cell" | "Move cell up" option. This will delete the first time stamp from the C column, so we get a time stamp showing in column B, and the text that relates to it in column C.
Now we shuffle over to column A, and enter the following wee formula (Trifuntov, 2023) into cell A2:
=ISODD(ROW(2:2))
What this does - thank you Alexander Trifuntov! - is to return a "TRUE" if the row is an odd numbered row; and a "FALSE" if the row is even numbered. We drag this formula down our page. It will consecutivise row numbers as it goes (another new word I have just invented!).
Our second-to-last step is to pop some headings into the spreadsheet: I use Odd rows; timestamp; and transcript to prepare for our auto-filter.
For our last step we highlight our data, and select auto-filter. Now we can (a) filter column A to "False", which then gives us our timestamp and text side-by-side in columns B and C (and as you can see by the image accompanying this post); and we can now search for "contains" in column C.
Sam
References:
Trifuntov, A. (2023, July 19). How to select every other row or every nth row in Excel. Able Bits. https://www.ablebits.com/office-addins-blog/select-every-other-row-excel/
No comments :
Post a Comment
Thanks for your feedback. The elves will post it shortly.