Welcome to Translation England: your first stop for quality Spanish to English translation, subtitling and proofreading services
Translation England
  • Home
  • About
    • Our Team
    • Our Services >
      • Translation
      • Subtitling
      • Dubbing + Voice-Over
      • Audio Translation
      • Audio Transcription
      • Proofreading
  • Contact us
  • FAQs
  • Español
  • Translation Blog
  • Sample Translations
  • Terms of Business
  • Latin American Stories Blog

How to export Subtitle Edit or .srt subtitle files into Microsoft Excel

7/1/2014

13 Comments

 
OK, so you've got a subtitling job.  Great!  But unlike your course tutor, the Project Manager doesn't want to receive a 'normal' subtitle file, like an .srt .ssa .stl file you might have been working with until now.

The Project Manager has asked you to return an Excel file.  In one column, you are required to put the time-ins, in the second, the time-outs, and in the third, you have to put the subtitles.  While it may be possible to directly export to an Excel file on professional software like Swift, Spot or EZTitles, if you are new to the subtitling industry and using an open source subtitling program like Subtitle Edit, you are not so lucky.

Fortunately, after much experimenting with the export setting on Subtitle Edit, I have found a way of exporting your subtitles to an Excel File, with the time-ins, time-outs and subtitles all in separate columns.
It is a bit long-winded, but it will save you having to type in manually ALL the timecodes and subtitles.  It is possible to export as a Plain Text file on Subtitle Edit, but then separating the three columns is not possible.  Until now I haven't found a quicker or simpler way.  Please let us know if you do!

OK, so you're working in Subtitle Edit, and have finished creating your subtitles.
Step 1:
Go to File > Edit > Export Custom Text Format

Step 2:
You'll get a pop-up box with 3 kinds of formats: SubRip, MicroDVD and New.
Click on the 2nd one: MicroDVD, and then click on the "Edit" button.

Step 3:
You'll get another pop-up box.
This next step is very important.  In the Text line (paragraph), you need to delete everything leaving only {start}. YOU MUST LEAVE A BLANK SECOND LINE BELOW {start} or the timecodes will all appear horizontally instead of vertically.
Also, in the "Time code" box, you have to change from 'ff' to 'hh:mm:ss.ff'
See the "before" and "after" images below for Step 3.
Imagen
"Before". This box appears after highlighting 'MicroDVD' and clicking on the 'Edit' button.
Imagen
"After". You must delete everything except {start} in the 'Text line (paragaph)', and remember to leave a blank second line. Also, change the Time code format to hh:mm:ss.ff
Step 4:
Click on OK. Then "Save as..." and there you go, you will have a file with only the time-ins.  You can later open this file in Microsoft Word and copy and paste the list into the Excel file.

Step 5:
Open the newly created file in Word.  You will notice that on the time-ins, between the seconds (ss) and the frames (ff), there is a period.  You should use the "Replace all" feature of Word to replace all examples of these periods to colons.  I am pretty sure the Excel file you send to the Project Manager will be useless if you send it with timecodes with a period in between the seconds and frames numbers.

Step 6:
Repeat Steps 1 to 5, but this time creating time-out and then subtitle files.  In Step 3, you'll need to leave only {end} to get a time-out only file, and {text} to get the subtitles in one file.

Like I said, this is a lengthy process, and is quite laborious when you are working with many short videos, but if you are using free subtitling software like Subtitle Edit, this is the only way (so far) I have found of creating an Excel file.

If you have any questions about this process, please feel free to ask.
Thanks, and good luck!
Jonathan Hemming
j.hemming@translationengland.com

Imagen
You can see above between the second and frame numbers, there is a period (full stop), not a colon.
Imagen
Using Word's handy "Find and replace" feature, you can change at once all those pesky periods to colons. Job done!
13 Comments
Michael Phelps
1/8/2014 02:42:31 pm

If you were to change the text line to the below AND change the New Line drop down to | you could import the saved text into Excel with delimiters of ; AND |. Note that there is still one line after {text} below.

{number};{start};{end};{text}

Reply
Jonathan Hemming link
2/8/2014 02:33:19 am

Hi Michael, thanks for your comment. I'm not clear at the minute how I should apply what you say, but I'll give it a go!

Reply
Michael Phelps
7/8/2014 10:23:19 am

I also changed the New Line entry on the 'Custom text format template' from a double-pipe '||' to a single pipe '|'. I then saved this as a text file. I imported this text file into blank Excel worksheet by selecting Import, text file, with delimiters of semicolon and Other. The Other is a single pipe (|). This will import each line into a separate column. This is useful if you don't want to lose the line break.

Jonathan Hemming link
8/8/2014 09:15:30 am

Hi Michael, thanks a lot! This will surely make life easier. I'm going to try it out.

Reply
Geniux
4/11/2014 08:31:52 pm

This tip is really useful for me. Thanks very much.

Reply
Bruce
19/12/2014 12:11:21 pm

In subtitle edit's options menu > Settings > Time Code Mode = HH:MM:SS:FF

I save as csv2 in Subtitle edit, then open Excel and click open (all file types), and import my file after carefully selecting Delimited >comma delimited > Finished. Column A subtitle number, B Time in, C Time Out, D subs.

Reply
Gustavo
18/5/2015 05:57:15 pm

Thank you all for the information. Bruce's option created for me an odd placement of the lines 2 of subtitles in the first column. I haven't found a way to place a two-line subtitle in the same cell, but discovered that saving the file as Swift.txt and importing it as text delimited by tabs, the two line subs appears as one line below the other, but in two different cells. In any case, it's the most elegant option I've found so far. I wonder what are the advantages of commercial subtitling software to justify their cost when Subtitle Edit has fairly everything, except change of scene detection...

Reply
Sabrina
29/6/2015 07:30:07 am

This post was very useful for me. Thanks a lot for sharing! But what if the opposite happens? The client sends me a time-coded template in Excel and I want to export it to be able to work with a subtitling software? Does anyone know how to do it?

Thanks again!

Reply
test
12/10/2015 11:09:56 pm

{number} {{start}} {{end}} {text}
[empty line]


^I use it that way

Reply
Jesko
15/4/2016 11:39:59 am

see this Video for an easier way:
https://www.youtube.com/watch?v=kRByADw3HIU

Reply
SLM
15/4/2016 11:11:08 pm

Genious, Jesko!

Reply
Jesko
16/4/2016 07:11:14 am

.... and if the original one has no special characters or (") you can also use csv2 as export format.

Reply
Jules
4/12/2017 03:26:41 pm

Hi! I come 3 years later, but thanks for the post, first of all. Really usefull. I just can't get the two lines-subtitles to spread into the two cells. I tried all of your suggestions, (exporting as Swift.txt, as csv2, what Jesko suggests...) Nothing works with the second line. Any ideas? Thank you!

Reply



Leave a Reply.

    Translation England

    We translate between Spanish and English.

    We do subtitles and proofreading too.

    Translation Blog Home:
    Click here!


    Archives

    May 2017
    June 2015
    May 2015
    March 2015
    February 2015
    August 2014
    July 2014
    June 2014
    January 2014

    Categories

    All
    Applied Translation Studies
    Audiovisual Translation
    Audiovisual Translation Studies
    CAT Tools
    Certified Translation
    Colombia
    CPD
    Freelance
    Institute Of Translation & Interpreting
    ITI
    Leeds University
    Luis Suárez
    MemoQ
    Peru
    Subtitle Edit
    Subtitler
    Subtitles
    Subtitling
    Trados
    Translation
    Translation Problems

    RSS Feed

Picture
Translation England Language Services
translationengland@gmail.com
Copyright © 2017 Translation England