Spreadsheets can calculate numbers pretty nicely and apply number formats to the resulting values:
Select the initial triples.
Tools>Text To Columns...
Split by semicolon.
You get 3 columns of numbers, say column A B C, starting in row #1
D1: =TIME(0;$A1+1;$B1)
Calculatates the time value from zero hours, A1+1 minutes and B1 seconds, disregarding the frames. TIME wraps surplus units as well as negative time values properly [TIME(10;-30;0) => 9:30 and TIME(10;90;0) => 11:30]
Once you work with numeric values, formatting is completely up to you.
12:30:00 is just the very same value as
12:30
0.52083 [fraction of a day, all dates and times are formatted numbers in unit "Days"]
or whatever format you like.
But I need the hours added for the format used in a script file that uses these numbers.
Use the TIME formula on another sheet and export to csv:
A1: =TIME(0;$Sheet1.$A1+1;$Sheet1.$B1) [format as you like]
B1: =$Sheet1.$D1 [the frame number]
File>SaveAs...
Type: Text (*.csv)
[X] Edit filter settings
Confirm file format warning (loss of features, ...).
[X] Export as displayed (the formatted time values)
Semicolon as column delimiter.
Code: Select all
00:01:07;0
00:01:27;0
00:01:55;0
00:01:59;0
00:02:01;0
00:02:06;0
00:02:12;0
00:01:00;0
2 fields derived from your semicolon-separated text values. First one is a time, second one is the untouched frame number.
Of course you could add the frame number to the time value as fractions of seconds based on a known frame rate.
Assuming a frame rate 30/sec, you would simply add the frames as a fraction of a day:
=TIME(0;$Sheet1.$A1+1;$Sheet1.$B1)+$Sheet1.$C1/24/60/60/30 and apply a number format such as "HH:MM:SS.00")
and get a single column:
Code: Select all
00:01:07.00
00:01:27.00
00:01:55.00
00:01:59.00
00:02:01.00
00:02:06.00
00:02:12.00
00:01:00.00
If you really want to keep semicolon-separated 2-digit fields for HH;MM;SS;FF
Calculate the time value in the original sheet, say column D in Sheet1 and create the csv values:
A1: =HOUR($Sheet1.$D1)
B1: =MINUTE($Sheet1.$D1)
C1: =SECOND($Sheet1.$D1)
D1: =$Sheet1.$C1
Format all figures with 2 digits (number format "00")
Alternatively:
A1: =TEXT(HOUR($Sheet1.$D1);"00")
B1: =TEXT(MINUTE($Sheet1.$D1);"00")
C1: =TEXT(SECOND($Sheet1.$D1);"00")
D1: =TEXT($Sheet1.$C1;"00")
and set nothing as text-delimiters in the export settings, otherwise you get quoted strings.
