Excel help with Times

[Tom]

When I import some htm files in Excel, the time for races is shown as
1:23.5, 1:09.3, 1:45.8

Is there an easy way to convert these to a format like 123.5, 109.3, 145.8?
I have to copy and paste special the HTM stuff onto a new worksheet using the Values paste option, in order for the rest of the data to be usable.
I tried to just find the ":" and remove them, but it did not work.

[wilderness]

Tom,
Html (web pages) may be opened with any text editor.
Suggest replacement prior to importing into Excel.

Would also suggest focusing upon more than the colon.

EX
Replace 1: with 1

[Tom]

Problem is, no matter what I do, the cell values are forced to be AM or PM.
This is not what I want - can't believe Excel can't handle times, not time of day, but every google hit has been dealing with times.

Custom formating is no help, text to columns no good.....I used to have a $20 spreadsheet that could handle times 20 years ago.
Wonder if it is still around? :lol:

[cj]

Easiest way is to make sure it is a text field as you import.

[wilderness]

Excel has a Custom Cell format of

mm:ss.0

Excel Date format also offers an exception with a leading asterisk.

I'm sure others will be along, I'm not a spreadsheet guy.

[DJofSD]

Easiest way is to make sure it is a text field as you import.
I agree -- I think the key, Tom, is to make sure the cell being used for the target of a conversion to a "Sartin style" representation of the split is string or text not a datetime.

[Tom]

I don't see any option to do that.
I tried import the htm file from the Data tab - it just goes in, no options.

What I usually do is save the file, then "open with" Excel.
Then I add a worksheet and copy all and paste special using Values option.

I have never imported any other way.

[DJofSD]

I don't see any option to do that.
I tried import the htm file from the Data tab - it just goes in, no options.

What I usually do is save the file, then "open with" Excel.
Then I add a worksheet and copy all and paste special using Values option.

I have never imported any other way.
I was thinking along the lines of writing a macro to convert it to a string.

[tupper]

It might be worthwhile to try make the colons a "separator," which would put the minutes and seconds in two separate columns.

[k f]

If you multiply that cell by 86,400 and change the format to either number or general, you'll get it in seconds. So, that 1:23.5 will come out as 83.47.

[cj]

I don't see any option to do that.
I tried import the htm file from the Data tab - it just goes in, no options.

What I usually do is save the file, then "open with" Excel.
Then I add a worksheet and copy all and paste special using Values option.

I have never imported any other way.

Send me a copy of one of the files you are importing via email.

[DJofSD]

Send me a copy of one of the files you are importing via email.
Or, a link to a page which is saved then opened with Excel.