Extracting the number of seconds from a variable text string

[JohnR99]

I have a column of cells, each with a with text string that has three possible formats where I want to extract the total number of seconds.

The three formats are:

nnn hrs nn min nn sec (example: 1 hrs 42 min 58 sec)
or
nn min nn sec (example: 55 min 1 sec)
or
nn sec (example: 10 sec)

The result I want is a numeric value of the total number of seconds in the text string.

For example a text string of: 1 hrs 42 min 58 sec

Should give the result: (1 x 3600) + (42 x 60) + 58 = 6,178

Any help would be appreciated.
Thanks
John

 

[gior]

After additional checking: incorrect results if "min" is missing – for such example "1 hrs 11 sec".<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Matt Rogers]

Hello.

My last try ...:

Excel Workbook
	A	B
1	1 sec	1
2	1 min 1 sec	61
3	1 min 01 sec	61
4	1 hrs 10 sec	3,610
5	10 hrs 1 min 1 sec	36,061
6	111 hrs 1 min 1 sec	399,661
Sheet

 

[Richard Schollar]

Barry's formula was awesome (no surprises there) - the following amendment takes account of no seconds in the calc:

=SUM(MID(0&A3&"000",FIND({"h","m","se"},A3&"xxxhmse")-2,2)*{3600,60,1})