Subtitles to a Spreadsheet, and Back: What Survives the Round Trip

Why a spreadsheet is the right shape for review

A subtitle file is a bad object to hand to somebody who is not a subtitler. It interleaves content and machinery, so a reviewer has to skip over numbers and timestamps to read a sentence, and any accidental edit to a timestamp is invisible until playback. One row per cue fixes both problems at once. The text sits in a single column that can be read straight down, the timings sit in their own columns where they can be seen but are unlikely to be touched, and a second column can be added for a translation, a comment or an approval mark without disturbing anything.

The columns this converter writes are index, start, end and text, in that order, with a header row of exactly those four names. The index is regenerated from the position of each cue in the file rather than copied from the source numbering, so a file whose counters are duplicated or out of sequence comes out cleanly numbered from one. Keep that column. On the way back it is read rather than ignored: when every row carries a distinct number the importer rebuilds in index order, so a sheet you sorted by text while working still produces a subtitle file in the right sequence.

Commas, quotation marks and the RFC that covers them

Dialogue is full of commas, and a comma is the field separator, so this is the first thing to check in any subtitle-to-CSV tool. RFC 4180, the 2005 memo that wrote down what CSV files usually look like, gives the rule: fields containing line breaks, double quotes or commas should be enclosed in double quotes, and a double quote inside such a field is escaped by preceding it with another double quote. This converter follows both halves. "Well, no, not exactly." is written as a single quoted field, and He said "the harbour", not the docks. is written as "He said ""the harbour"", not the docks." — quoted because it contains both a quote and a comma, with each inner quote doubled.

The timestamps get the same treatment without anyone deciding it, and the reason is a pleasant accident of the format. SubRip writes its milliseconds after a comma, so 00:00:01,000 contains a field separator and is therefore quoted on every row of every file this tool produces. It is worth noticing because it is the one place where SubRip's odd choice of separator does you a favour: a WebVTT timestamp, which uses a full stop, would go out unquoted and would be at the mercy of whatever the reading program decides an unquoted 00:00:01.000 is.

Two honest deviations, neither of which will hurt you but both of which are real. RFC 4180 says records are delimited by a carriage return and line feed; this converter writes a bare line feed, which every spreadsheet accepts and which matters only if something downstream is very literal-minded. And the RFC is Informational, not a standard — it describes what implementations do rather than mandating it, and its own wording is "should" rather than "must". Treating it as the definition of CSV is a useful convention, not a guarantee about the next program that reads your file.

Four things to know about the spreadsheet in the middle

The delimiter. Spreadsheets in locales that use the comma as a decimal separator — which is to say French, German, Spanish, Italian and Portuguese ones — commonly save a file with semicolons between fields instead of commas, following the system list separator. The importer looks at the first record and counts the separators that fall outside the quotes, so such a file is read rather than refused; a semicolon inside a cue is not counted, because it sits inside a quoted field. Choosing the comma-separated variant explicitly, when your spreadsheet offers it, still gives the tidier file — but a plain save in one of those five languages no longer costs you the import.

The types. CSV carries no type information at all — RFC 4180 says nothing about data types, because there is nothing to say: every field is text. Quoting protects the parsing, not the meaning. A spreadsheet reads the quoted field, unwraps it, and then decides for itself what the contents are, which is where a timestamp is at risk: 00:00:01,000 is a string that a program is entitled to read as a time, as a number with a thousands separator, or as text, depending on its locale and its guesses. Nothing in the file tells it which. The reliable habit is to import rather than open — use the spreadsheet's text-import path and set the two time columns to Text before the data lands — and to check one timestamp before you start editing.

The formulas. A cue whose text begins with an equals sign, a plus or an at sign — and a minus followed by a digit or another operator, as in -1-1 — is written out as a quoted field with an apostrophe in front of it, which is the escape a spreadsheet already understands: the cell shows the text and evaluates nothing. The importer strips that apostrophe again, so =SUM(A1:A9) round-trips as itself. A dialogue dash is deliberately left alone, because a line beginning “- Yes.” is speech in every second subtitle file and is a formula in no spreadsheet. What this does not protect you from is a formula you type yourself while editing.

The order. Sorting is the most natural thing to do in a spreadsheet, so the importer reads the index column rather than ignoring it: when every row carries its own distinct number, the output is rebuilt in index order and a note tells you the rows were put back in sequence. Sort by text to group similar lines for a terminology check and the rebuilt SRT still comes out in time order. If the numbers are duplicated, or missing on some rows, the column cannot be trusted — row order is used instead and the tool says so. Backwards timings are reported the same way: a row reading 00:00:09,000 to 00:00:04,000 is still written out as given, but it is now counted and named rather than passed through in silence.

Frequently asked questions

Does a comma inside a subtitle break the CSV?

No. Any field containing a comma, a double quote or a line break is enclosed in double quotes, and a double quote inside such a field is escaped by doubling it, which is what RFC 4180 describes. Tested with real cases: "Well, no, not exactly." becomes a single quoted field, and He said "the harbour", not the docks. becomes "He said ""the harbour"", not the docks." — quoted because it holds both a comma and a quote, with each inner quote doubled. Both come back byte-identical through the CSV to SRT converter, and they survive three consecutive round trips without accumulating quotes, which is the usual failure of a naive escaper. The timestamps are protected by the same rule without anybody choosing it, because SubRip writes milliseconds after a comma and 00:00:01,000 therefore contains a delimiter.

Do two- and three-line cues survive the round trip?

Yes, and the reason is worth knowing because it is where this kind of tool usually fails. A cue containing a line break is written as a quoted field with a real newline inside it, spanning two physical lines of the CSV file — the correct form under RFC 4180, and the one a spreadsheet opens as a single cell. The CSV to SRT converter reads the file as CSV records rather than as text lines, so a newline ends a record only when it falls outside the quotes; a record spanning three physical lines is still one record. Verified on the exporter's own output: a file mixing one-line, two-line and three-line cues, one of them containing a comma and one a quotation mark, rebuilds byte-identical, and stays byte-identical after three consecutive round trips. If you edit the text in a spreadsheet, use its in-cell line break — Alt+Enter in Excel on Windows — rather than typing a backslash and an n, which is just two characters of text.

My spreadsheet saves the file with semicolons. Will it import?

Yes. Spreadsheets follow the system list separator, and in locales that use the comma as a decimal mark — French, German, Spanish, Italian, Portuguese — that separator is the semicolon, so a plain save produces a semicolon-delimited file. The importer looks at the first record of what you paste, counts the commas and semicolons that fall outside quoted fields, and uses whichever is more common; a semicolon inside a line of dialogue is not counted, because it sits inside the quotes. That means a file saved from a French, German, Spanish, Italian or Portuguese spreadsheet imports as it is. Choosing the comma-separated variant explicitly — usually offered under a name like CSV UTF-8 or comma-delimited — is still the tidier habit if you are going to pass the file to something else, but it is no longer a condition of the file working here.

Will Excel destroy my timestamps by reading 00:00:01,000 as a time or a number?

It can, and the quoting does not prevent it, because quoting and typing are two different problems. RFC 4180 says nothing whatsoever about data types — a CSV field is text, and the format has no way to say otherwise. The quotes tell a reader where the field begins and ends; once the reader has unwrapped it, what it decides the contents mean is entirely up to it, and a spreadsheet's guess depends on its locale as well as on the string. Do not rely on luck: use the spreadsheet's text-import route rather than double-clicking the file, and set the start and end columns to Text before the data lands. Then check one timestamp in the sheet, before you edit anything, and confirm it still reads 00:00:01,000 rather than a right-aligned value with a different shape. If it has been converted, close without saving and import again — repairing a column of mangled times is far more work than importing it correctly the first time.

Can I add, delete or reorder rows in the spreadsheet?

Add and delete freely; reorder freely too, as long as you keep the index column. The importer reads that column: when every row carries its own distinct whole number, the output is rebuilt in index order and a note tells you the rows were put back in sequence, so a sheet sorted alphabetically by text to group similar lines still produces a subtitle file in time order. If some rows have lost their number, or two rows share one, the column cannot be trusted — the tool then keeps the order the rows appear in and says so rather than guessing. Deleting a row is safe: the output is renumbered from one whatever the surviving indexes are. The importer also now flags a cue whose end time is earlier than its start — a row reading 00:00:09,000 to 00:00:04,000 is still written out as given, but it is counted and named instead of passing in silence. Times may also be entered as plain seconds rather than timecodes, which is convenient when you are adding a cue by hand: 5.5 and 8.2 are read as 00:00:05,500 and 00:00:08,200.