Tag: Excel

This post is the last in the series on Captioning and Transcription. The first two posts cover how to decide what to edit, and tips and tricks for faster captioning. This post builds upon the latter, and covers how to create transcripts more quickly.

If you need to produce captions and a transcript, complete the caption editing first and then use the corrected caption file as your starting point for your transcript. Creating a transcript with the method below will not give you a file that can be used for captions.

When creating a transcript, you will need to decide a few things before you start.

• Are timings important, and if so, what level of accuracy do you need? Does every utterance need a time, or would it suffice to signal timing points at the start of each slide, for example?
• Is it important to know who is speaking? Training videos may not need this.

The first step is to download your caption file and copy and paste the text into Word. Then, transform your lines of text into a table using ‘convert text to table‘ and setting the number of columns to 4.

Copy your table from Word and paste it into Excel. Once this is done, you can use Excel to trim down your times and make them more human-friendly, using the MID function in a new column. Copy the formula down the whole row to quickly tidy all of the timestamps. This example trims the hours from the start time, and gets rid of the milliseconds and the end time completely.

Pasting the resultant data back into Word means you can then use Word’s formatting tools to shape your transcript, adding columns as needed to denote speakers or other important information, and removing columns that contain redundant data.

Cutting and pasting the individual cells containing speakers’ words into a single cell will help improve the look of the document. Paste without formatting to combine the cells into one. Spare rows can then be deleted. Using Find and Replace to get rid of line breaks (search for ^p) and replacing them with spaces further tidies this up. You can then delete the redundant rows.

If you like, you can then remove the borders from your table. You could also set a bottom cell margin to automatically space out each row.

It is important to check your resulting transcript’s accessibility. As this method uses a table, make sure that a screen reader can read it in the correct order. You can do this by clicking into the first cell of your table and then pressing the tab key to move between cells – this is the order most screen readers will relay the text, so check it makes sense.

You can also choose to add a header row to your table to give each column a title. After adding this, highlight the row, click ‘table properties‘ and in ‘row‘ select ‘repeat as header row’. Ally in Canvas will not give a 100% score without this.

Once your transcript is formatted correctly, you can correct your text and fix mistakes in the usual way if you have not already done so for captions. The previous post on Faster Captioning has some tips and tricks to speed this up.