Preparing Your Transcript

Paste Your Transcript Into Google Sheets

Create a new Google Sheet
In the first row, title four columns timestamp, speaker, words, and tags
In a new browser tab, open your transcript as a Google Doc
Copy all of the text of the transcript (leave out any biographical information at the beginning)
Switch back to the Google Sheet you created
Click on the cell underneath speaker
Click on Edit > Paste Special > Paste Values Only

This should paste all the transcript text into the rows of the second column
Note: Your transcript rows should not have a space between them. If you do have spaces between your rows, see below.

If the text you paste into your Google Sheet looks like this…

…you need to remove the space between paragraphs in your Google Doc. Follow the steps below:

In the top right of the screen, look for the line spacing button (three horizontal lines next to a vertical arrow). In the dropdown options, click on Custom spacing.

A Custom spacing popup will appear. Make sure the text boxes to the right of Before and After have values of 0. Click Apply:

Open Find and Replace:
- Press ctrl + f (on PC) or cmd + f (on Mac)
- A search box should appear in the top right of your screen. Click on the More options button to the right of the search box (three vertical dots). A box labeled Find and replace will appear in the middle of your screen.

In the Find box, enter the initials of the first speaker (ex. RS)
In the Replace box, enter the initials of the first speaker, plus an asterisk (*) (ex. RS*)

Click the Replace all button. Then click Done
Repeat the steps above for the initials of the second speaker and the speakers’ full names
Your spreadsheet should now look like this:

If they aren’t selected already, select the cells you just pasted, so that your sheet looks like this:

A little box labeled Separator: will appear near the bottom of your screen. Click on the arrows to the right of Detect Automatically

Make sure your asterisk is followed by a space! Otherwise your transcript might not work right for the website.

There is likely to be some text that accidentally got separated out to extra columns. To easily find this, select the entire spreadsheet so it’s highlighted in blue
Click on Format > Text wrapping > Clip

Copy and paste this text back into the appropriate words cells (you’ll likely need to refer back to the Google Doc version of the transcript to verify that you’re putting it in the right place)
When this text is cleaned up, change the formatting to “Wrap” by selecting the entire spreadsheet again and clicking Format > Text wrapping > Wrap.
Expand column C (the words column) so you can comfortably read the text. Your transcript should now look something like this:

Now you’ll need to read through your interview with three goals:

If you encounter an especially large cell of text, you can create a new row below that cell and separate the text into two parts
To separate a cell like this:

Insert the speaker’s initials into the new speaker cell
Select and copy half the text from the original words cell, and paste it below into the cell you just created (make sure to also delete the text you just pasted from its original cell)
Finished, your split cell should look like this:

Timestamps are located periodically throughout the interview at the end of the text in the words cells:

Name your transcript spreadsheet file using the following format with the interviewee’s preferred name: lastnamefirstname (all lowercase) (example: bergmannroger)
If the interviewee has elected not to use his or her whole last name, use the first initial of their last name in place of their last name: initialfirstname (example: broger)
If the interviewee has elected not to use his or her last name at all, simply title the transcript with their firstname: firstname (example: roger)

Create a new Google Sheet
Copy and paste the contents of the sheet you just created into this new sheet
Name this new sheet according to the naming conventions above, adding _original to the end of the file name
Set this _original sheet aside; it will serve as the full transcript spreadsheet for the archives