I need help on Google Sheets/Excel.

I extracted sports results data with the IMPORTHTML function of Google Sheets.

All the cells in the Result column contain a duration in the form of "m:ss.00" but stored as text (eg: 3:34.56 for 3mn, 34sec, 56).

How can I convert this data to an actual duration format (mm:ss.00)?


  • Why Excel? The IMPORTHTML is only available in Google Sheets. – Jacob Jan Tuinstra Sep 21 '14 at 18:51

Have you tried multiplying your text by 1? That should convert it to a number (alternatively use the VALUE() function)

You might then want to use formatting to format your (very small) number to look like you want it to

  • -1; have you tried that yourself first? – Jacob Jan Tuinstra Sep 21 '14 at 18:52
  • @JacobJanTuinstra, I tested it in Excel, as that was all I had access to. Since I gave two options to convert text to number I would be surprised if Google didn't support either. I gave an answer because no one else had. Would you say it's better to not leave OP without an answer because if none of my suggestions work I might get downvoted? – CallumDA Sep 22 '14 at 7:49
  • Suggestions is not what the OP is after. If I were to make suggestions, I would leave a comment. – Jacob Jan Tuinstra Sep 22 '14 at 9:02
  • And if I had enough rep at the time it would have been a comment. Regardless though, no one should be downvoted for trying to help. That's what this community is built on. Least of all because they put their suggestion in the wrong place. Can you actually give OP an answer, or do you just troll SU looking for answers you can downvote? – CallumDA Sep 22 '14 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.