CODEWITHSUNDEEP
excelexcel-formula
GreenSquirrel
GreenSquirrel

Reputation: 19

Convert string to time in excel

In my excel sheet I have a column with time. But the time is given in the following format.

1m 10.37s
2m 8.42s

I want to convert this to hh:mm:ss

Just can't figure out how.

Thank you for your help.

Upvotes: 0

Views: 73

Answers (1)

JvdV
JvdV

Reputation: 75840

You could try:

enter image description here

Formula in C1:

=--("0:"&SUBSTITUTE(LEFT(A1,FIND(".",A1)-1),"m ",":"))

Obviously cells have been numberformatted hh:mm:ss.

Edit:

=TIME(0,IFERROR(LEFT(A1,FIND("m",A1)-1),0),-LOOKUP(1,-MID(A1,FIND(" "," "&A1),ROW($1:$99))))

Upvotes: 2

Related Questions