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]

Hello John.

Maybe like this:

Excel Workbook
	A	B
1	01:42:58	6.178
2	01:00:00	3.600
3	02:00:00	7.200
Sheet

 

[JohnR99]

Unfortunately this approach will not work. The text string is in the format as shown in the original post (eg: 1 hrs 30 min 20 sec) and has three possible formats depending on the length of the time. The string is not in a format that can be multiplied. Thanks.

 

[Richard Schollar]

Howdy John

Following seems to work - someone may be a long with a smarter (and shorter) solution though:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td width="25px" style="color: #161120;text-align: center;">1</td><td width="63px" style=";">1 hrs 42 min 58 sec</td><td width="63px" style="text-align: right;;">6178</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">2</td><td width="63px" style=";"> 55 min 1 sec</td><td width="63px" style="text-align: right;;">3301</td></tr><tr ><td width="25px" style="color: #161120;text-align: center;">3</td><td width="63px" style=";"> 10 sec</td><td width="63px" style="text-align: right;;">10</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.9E+307,86400*RIGHT(<font color="Red">"00:00:"&SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">SUBSTITUTE(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">A1," ",""</font>),"hrs",":"</font>),"min",":"</font>),"sec",""</font>),ROW(<font color="Green">INDIRECT(<font color="Purple">"1:"&LEN(<font color="Teal">A1</font>)</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[RomulusMilea]

Hello,

I have created for you a "monster" formula that will work for all 3 cases, I assumed input data is in the cell C18 (you need to adapt it to your needs):

=IF(C18="","No input data !",IF(LEN(C18)-LEN(SUBSTITUTE(C18," ",""))=0,"Incorrect input data !",IF(LEN(C18)-LEN(SUBSTITUTE(C18," ",""))=5,3600*LEFT(C18,FIND(" ",C18)-1)+60*MID(SUBSTITUTE(C18," ","@",2),FIND("@",SUBSTITUTE(C18," ","@",2))+1,FIND("#",SUBSTITUTE(C18," ","#",3))-FIND("@",SUBSTITUTE(C18," ","@",2))-1)+1*MID(SUBSTITUTE(C18," ","@",4),FIND("@",SUBSTITUTE(C18," ","@",4))+1,FIND("#",SUBSTITUTE(C18," ","#",5))-FIND("@",SUBSTITUTE(C18," ","@",4))-1),IF(LEN(C18)-LEN(SUBSTITUTE(C18," ",""))=3,60*LEFT(C18,FIND(" ",C18)-1)+1*MID(SUBSTITUTE(C18," ","@",2),FIND("@",SUBSTITUTE(C18," ","@",2))+1,FIND("#",SUBSTITUTE(C18," ","#",3))-FIND("@",SUBSTITUTE(C18," ","@",2))-1),1*LEFT(C18,FIND(" ",C18)-1)))))

Formula is quite sensitive to input data format, if the input cells do not follow the patterns defined by you, then formula may generate #VALUE! error, or other error.

I hope these help.

Please confirm the formula resolves your problem, as expected.

 

[JohnR99]

Hi Richard

That works perfectly. Wow! Now all I have to do is study what you have done so I full understand it.

Thanks for your help.

Regards
John

 

[villy]

Try this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," hrs ",":")," min ",":")," sec","")*86400

 

[JohnR99]

Hi Villy

Thanks for your solution. However, it does not give the right results.

Thanks
John

 

[Matt Rogers]

Hi JohnR99

Try this:

Excel Workbook
	A	B
1	5 hrs 19 min 33 sec	19.173
2	19 min 33 sec	1.173
3	33 sec	33
4	15 hrs 19 min 33 sec	55.173
5	115 hrs 19 min 33 sec	415.173
Sheet

 

[gior]

To JohnR99:

If your example "55min 1 sec" is possible - instead of "55 min 01 sec".
(Last solution doesn't work in this case).