Extract date and time from pasted cell data to use in formulas

[kmsumrall]

I copy and paste the following data from the UBER website.

Data before copy and paste:
TIME DURATION MILEAGE FARE
October 31, 2014 12:50PM PDT 37:05 8.76 $19.22
October 29, 2014 4:20PM PDT 12:18 4.3 $9.11
October 29, 2014 3:58PM PDT 15:24 4.37 $9.84
October 29, 2014 11:34AM PDT 54:39 27.88 $43.94

Data after it is pasted into excel:
TIME DURATION MILEAGE FARE
October 31, 2014 12:50PM PDT 37:05:00 8.76 $19.22
October 29, 2014 4:20PM PDT 12:18 4.3 $9.11
October 29, 2014 3:58PM PDT 15:24 4.37 $9.84
October 29, 2014 11:34AM PDT 54:39:00 27.88 $43.94

Notice that excel changes some of data:
The “Duration” 37:05 becomes 37:05:00
But 12:18 remains 12:18

I cannot get excel to use this data as true HRS:MIN: SEC. Sometimes the Duration may be HRS:MIN: SEC, but most of the time just MIN:SEC as above.

I am also having difficulty extracting the DATE as a date number and the TIME as the true time. No space between the AM and PM text has been an issue.

The goal is to have formulas in an excel sheet that will utilize the data pasted into the sheet as is.

 

[Scott Huish]

I don't understand. 37:05 was in both sets, not just the second one.

 

[kmsumrall]

the first one was 37:05 and the other was 37:05:00
It changes the date from MIN:SEC TO HRS:MIN:SEC