Excel - Converting Text in a result

Question

I'm looking for some tips to transform a result I have in a cell from text to long.

In Cell A1 I have : "8 days 5 hours 10 minutes" In cell B1 I have : "8*86400+5*3600+10*60" which I have with some "substitue"'s function

here is the function : ""=SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;" days";"*86400");" hours";"*3600");" minutes";"*60");" ";"+")""

In cell C1 i would like the result

My problem is I need this "8*86400+5*3600+10*60" to be "709800", and I can't find a way to reach it.

I thought it was about formating my cells but I tried everything and it didn't worked. I can bet I tried all excel functions...

I'm stuck actually and looking for some good ideas.

Thank you for your help or advices !

File_picture

Answer 1

Here is a formula to perform the calculation from your string. It relies on the format as you have above, with a single space between each item in the string.

=SUMPRODUCT(--TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),{1,198,396},99)),{86400,3600,60})

The formula creates an array of the relevant values, and then applies the appropriate multiplier.

If there might be more than one space between each segment, then use:

=SUMPRODUCT(--TRIM(MID(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),{1,198,396},99)),{86400,3600,60})