0

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

dOyL
  • 25
  • 3
  • You won't get **executable formula** like this. Only **formula text.** – ttaaoossuuuu Nov 12 '14 at 10:53
  • Is it possible to use **C1** to execute this text ? This is a solution but i can't find a way to apply it – dOyL Nov 12 '14 at 10:54
  • Only with VBA. See this http://stackoverflow.com/questions/4471884/turn-a-string-formula-into-a-real-formula – ttaaoossuuuu Nov 12 '14 at 10:58
  • Oh Thank You so much, i didn't find it on Stack, i suppose i didn't use the best keywords. it's working now ! – dOyL Nov 12 '14 at 11:07
  • http://stackoverflow.com/questions/11967493/microsoft-excel-convert-text-to-formula-value55 shows without vba approach as well. – ZAT Nov 12 '14 at 12:12
  • i tried it before i use VBA and it didn't worked for me. But thank you for your help – dOyL Nov 12 '14 at 12:26

1 Answers1

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})
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60