Equation in text format convert to value

[madocar]

Is there a way to convert string (equation) to equation able to be calculate.

Original format (column A):

22 y 180 d 5 h

Required format

8210,208333 (days)

Substitute formula helps a lot (column B).

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A9;" y ";"*365+");" d ";"+");" h";"/24")

But I'm still unable to convert equation in text to value.
More in attachment

Thanks for any help

 

[NARAYANK991]

Hi ,

You can make use of the technique explained here :

https://www.vertex42.com/ExcelArticles/evaluate-function.html

See the attached file.

Narayan

 

[Chihiro]

Personally, I'd just split column using Text To Columns and do calculation.

@NARAYANK991
Neat way to leverage Evaluate. I've got to remember this one.

@madocar
If for some reason you are not able to use macro enabled workbook...
=IFERROR(LEFT(A9,FIND("@",SUBSTITUTE(A9,"y","@",1))-1),0)*365+MID(A9,FIND("@",SUBSTITUTE(A9,"d","@"))-4,3)+MID(A9,FIND("d",A9)+2,FIND("h",A9)-FIND("d",A9)-2)/24

 

[madocar]

Thank you @NARAYANK991 @Chihiro

 

[John Jairo V]

Hi to all!

Another option could be:

=SUMPRODUCT(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"y",REPT(" ",99)),"d",REPT(" ",99)),"h",REPT(" ",99)),1+{0;1;2}*99,99))*{8760;24;1})/24

Blessings!

 

[Chihiro]

Damn. That's neat use of REPT().

 

[bosco_yip]

Another possible......

=SUMPRODUCT(IFERROR(0+TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),{1,3,5}*99-98,99)),0)*{8760,24,1})/24

Regards
Bosco