How to convert Excel to SRT format and merge the time back in using Subtitle Workshop?
This guide attempts to explain how to take this Excel file with the translation for the movie that you are interested in. You will then see how to merge it in with the correct timing and save it into the correct format for your viewing plesaure.
Necessary Software: Subtitle Workshop 2.51
1 Open up the Excel file that your translator has given you. It should look something like this
2 Right click on the A column and select Insert.
3 The above action will cause a new column to be inserted before the A column and the new column will be called A and the original A column will be B.
4 In row 1 of Column A, copy or type in the following
a. Dialogue: Marked=0,0:00:09.56,0:00:11.79,Default,NTP,0000,0000,0000,!Effect,
b. Note: You might be thinking why you are filling this in. This is a generic timestamp placeholder that Subtitle Workshop will understand when trying to read this file later on when you are merging the timing with this translation file.
5 Select Column A, Row 1 and then scroll down to the last line and while pressing the Shift button, click on the last line.
6 Press CTRL+D, to fill the entire column down with the same value in Column A, Row 1.
7 Press Ctrl+S to save the file.
8 Now go to the File menu, and do a Save As.
9 Save this file as a Tab Delimited Text file.
10 You might get the following message, just click OK.
11 You might also get the following message box also, just click YES.
12 Close Microsoft Excel, you might be prompted with this dialog box, just click NO.
13 Open up the TXT file that you just saved in Notepad.
14 Press Ctrl+H and do a search and replace for "Dialogue:
Marked=0,0:00:09.56,0:00:11.79,Default,NTP,0000,0000,0000,!Effect," and replace it eith Dialogue:
Marked=0,0:00:09.56,0:00:11.79,Default,NTP,0000,0000,0000,!Effect,
15 Click Replace All.
a. You will know this is done when the hourglass disapperas.
b. Note: You might be thiking why you should do this Search and Replace. This is because Excel added Quotes around this field, and you need to get rid of it before you merge this in with the real timing in Subtitle Workshop.
16 Click Cancle on the replace dialog box.
17 Press CTRL+S to save the file.
a. One thing to note, if this is not an English translation, you might need to do a Save As to save this as a UTF-8 Encoded file.
b. Example of an English text file with ANSI encoding.
c. Select the Encoding to UTF-8 if this is ont an English file. Do not choose Unicode because Subtitle Workshop does not understand this encoding and will toss up an error.
18 Close notepad.
19 Open up the SRT timing file in Subtitle Workshop.
20 Click on the Edit menu, and select Texts, and then Read Text from File or press Shift+Ctrl+X.
21 Select the translated file text from Step 9 and click Open.
22 You will now see that the translated text is now merged with the timing.
23 Press Ctrl+S to save the file.
24 Close Subtitle Workshop.
25 You can then send this file on to your Quality Checker or Editors to check for any translation mistake etc. After this, you can release this as a softsub file by uploading this up somewhere (such as D-Addicts.com) for viewers to download or you can use a software program to hardcode this file with its corresponding episode.
Note: If you are using this as a soft sub, make sure the final SRT file name is named the same as the movie file that you want to watch and that both are located in the same directory. You will also need a program like VOBSub to watch it.
26 This concludes this guide.