How to convert time in text i.e. 19minutes and 40 seconds to time

I have a large file with duration in the format 19 minutes and 40 seconds or 1 hour, 37 minutes and 16 seconds etc. I need to convert this to time to perform calculations like average. I have tried Data -> text to column, doesn't help. Thanks in advance.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Enter array formula in B2 and copy down

Formula:

=--TEXT(NPV(-0.9,,IFERROR((MID(A2,256-COLUMN(A:IV),1)%),"")),"00\:00\:00")

Format as Custom, hh:mm:ss

v	A	B
1	Total Duration
2	19 minutes and 40 seconds	00:19:40
3	11 minutes and 24 seconds	00:11:24
4	42 minutes and 19 seconds	00:42:19
5	11 hours, 4 minutes and 42 seconds	01:14:42
6	43 minutes and 31 seconds	00:43:31
7	29 minutes and 20 seconds	00:29:20
8	36 minutes and 1 second	00:04:01
9	28 minutes and 37 seconds	00:28:37
10	1 hour, 25 minutes and 26 seconds	01:25:26
11	23 minutes and 30 seconds	00:23:30
12	23 minutes and 44 seconds	00:23:44
13	40 minutes and 48 seconds	00:40:48
14	30 minutes and 13 seconds	00:30:13
15	26 minutes and 53 seconds	00:26:53
16	1 hour, 1 minute and 1 second	00:01:11
17	26 minutes and 44 seconds	00:26:44
18	39 minutes and 20 seconds	00:39:20
19	44 minutes and 45 seconds	00:44:45
20	1 hour, 37 minutes and 16 seconds	01:37:16
21	40 minutes and 57 seconds	00:40:57
22	30 minutes and 53 seconds	00:30:53
23	1 hour, 24 minutes and 45 seconds	01:24:45
24	2 hours, 6 minutes and 52 seconds	00:26:52
25	38 minutes and 8 seconds	00:04:28
26	43 minutes and 32 seconds	00:43:32
27	1 hour, 18 minutes and 18 seconds	01:18:18
28	1 hour, 22 minutes and 9 seconds	00:12:29
29	30 minutes and 48 seconds	00:30:48
30	1 hour, 19 minutes and 42 seconds	01:19:42

AlKey, that is very clever.

I'm sure there's a far more elegant way to do it but this formula worked for me.

=TIMEVALUE(IF(ISERROR(SEARCH("hour", A2)), "00:", "")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"and ",""), " seconds", ""), " second",""), " minutes ", ":"), "minute ",":"), " hour, ", ":"), " hours, ",":"))

@ AlKey: Oops: Lines 16, 24, 25, 28

@ Norie: Nice, but fails for "1 hour and 1 second"

Originally Posted by shg

@ AlKey: Oops: Lines 16, 24, 25, 28

@ Norie: Nice, but fails for "1 hour and 1 second"

Yea, there are quite few that fail It happens when strings contains single numbers like 1 instead of 01. Maybe corrections can be made at the source.

hey AlKey, I like it and was thinking of borrowing it but I'm wondering if the formula breaks at row 5 where it should read 11:04:42 but instead comes out as 01:14:42?

It's still a clever formula, and I don't know how it works.

Originally Posted by shg

It's still a clever formula, and I don't know how it works.

I found this formula (and it's variants) long time ago but I was never able to find a person who wrote it. Neither I was able to find any explanation on how this formula really works. I used it on few rare occasion when I needed to pull numbers out strings.

Well, my solution is almost identical to @cbatrody in post#10, though a little shorter. I also included error checking for zero seconds.

=TIME(IFERROR(LEFT(A2,FIND("h",A2)-2),0),IFERROR(MID(A2,FIND("m",A2)-3,2),0),IFERROR(MID(A2,FIND(" s",A2)-2,2),0))

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee