Convert text into time

[satchy321]

Hello.

I've looked around but I can't seem to find what I'm specifically looking for

This may sound like a basic question but I'm trying to convert a text string into a time as below

A1 is "65 hr 29 min 38 sec"

B1 to be "65:29:38"


I tried B1=TIMEVALUE(SUBSTITUTE(A1," hr "," min "," sec ")) but it returns #VALUE

Thanks
G

[satchy321]

Found my answer
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," hr ",":")," min ",":")," sec","")

[Pete_UK]

The SUBSTITUTE function will return a text value, so perhaps you should do it like this:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," hr ",":")," min ",":")," sec","")

where the double minus will convert it to a proper time value. Apply a Custom format of [hh]:mm:ss to the cell, to prevent the hours from wrapping at 24.

Hope this helps.

Pete

[AlKey]

All formulas offered above will only work if each cell would contain hours, minutes and seconds.
Enter this formula in cell B1 and copy down. Format cells as Custom, [hh]:mm:ss

Please Login or Register  to view this content.

v	A	B
1	65 hr 29 min 38 sec	65:29:38
2	25 min 55 sec	00:25:55
3	15 sec	00:00:15
4	05 sec	00:00:05
5	2 sec	00:00:02
6	1 min	00:01:00
7	45 hr 31 min	45:31:00