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

 

[Matt Rogers]

Hi.

It's better now?

Excel Workbook
	A	B
1	55 min 01 sec	3.301
2	55 min 1 sec	3.301
3	115 hrs 09 min 01 sec	414.541
4	115 hrs 9 min 01 sec	414.541
Sheet

 

[barry houdini]

Try this formula to accommodate any values up to 99 hrs 59 min 59 sec

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

 

[RomulusMilea]

Howdy John

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5" width=10>B1</TH><TD style="TEXT-ALIGN: left">=LOOKUP(9.9E+307,86400*RIGHT("00:00:"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"hrs",":"),"min",":"),"sec",""),ROW(INDIRECT("1:"&LEN(A1)))))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Hello Richard,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Your solution is a beautiful surprise to me. If I evaluate the formula step by step I obtain:<o></o>
<o></o>
=LOOKUP(9.9E+307,{259200;#VALUE!;7380;#VALUE!;3723;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})<o></o>
<o></o>
For this particular case, LOOKUP function used is the ARRAY LOOKUP function. I was wondering why the LOOKUP returns the value of 3723, instead of 259200. As per Excel theory, the function should have returned 259200 value, because this value is the largest numerical value in the entire array, less than 9.9E+307.<o></o>
<o></o>
Please explain why it does not work like that. Thank you.

<o>Romulus.</o>

 

[Matt Rogers]

Hello barry houdini,

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

Wow, very well !

 

[barry houdini]

...As per Excel theory, the function should have returned 259200 value, because this value is the largest numerical value in the entire array, less than 9.9E+307...Please explain why it does not work like that.

Hello RomulusMilea

LOOKUP will return the greatest value that's less than or equal to the lookup value in a range or array sorted ascending, here the array isn't sorted....

=LOOKUP(BigNum, LookupArray)

will always return the last numeric value in LookupArray. Aladin explains here

 

[Matt Rogers]

To JohnR99:

VBA-Code is allowed?

 

[JohnR99]

Hi Barry Houdini

Your formula =SUM(MID(0&A1&"000",FIND({"h","m","se"},A1&"xxxhm")-2,2)*{3600,60,1}) work very well except for one situation where there is an exact number of minutes and the seconds are not shown. (eg: 5 min) I did no know of this possibility until I looked at the data that is passed from an external source.

Thanks
John

 

[JohnR99]

Hi Screwdriver

Unfortunately, VBA code is not allowed.

Thanks
John

 

[gior]

Pls try formula

=24*60*60*TIMEVALUE(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(IF(ISERROR(FIND( "hrs", A1)), "0:", "") &IF(ISERROR(FIND( "min", A1)), "0:", "") &A1, "hrs", ":"), "min", ":"), "sec", ""))

 

[JohnR99]

Hi Gior

Yes your formula works perfectly. Thank you. John