# Better way to calculate string formula in excel 2016/O365?

GOAL: evaluate "2 Days 16 Hours 30 Minutes" to seconds.

I searched the web and found no fitting answer, i desire a 100% formula solution. Limitations: no VBA, avoid name manager, only day/hour/minute, spaces exist.

current solution i have working: Use this SUM as an Array Formula:

``````=SUM(
(ISNUMBER(SEARCH("Day",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW(\$XFD\$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))*999,999)))))*(IFERROR(60*60*24*TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW(\$XFD\$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*999+1,999)),0))
+ (ISNUMBER(SEARCH("Hour",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW(\$XFD\$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))*999,999)))))*(IFERROR(60*60*TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW(\$XFD\$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*999+1,999)),0))
+ (ISNUMBER(SEARCH("Minute",TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW(\$XFD\$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))*999,999)))))*(IFERROR(60*TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",999)),(ROW(\$XFD\$1:INDEX(XFD:XFD,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))-1)*999+1,999)),0))
)
``````

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when Exiting edit mode.

This iterates each word in the string. If it finds `Day(s)`,`Hour(s)`,Or `Minute(s)` It does the proper multiplication with the number in front of that word. Then it adds the results. • i forgot to mention that i desire to avoid array formulas so i accept the answer. Nice & quick reply! – Ragnar Von Lodbrok May 22 '19 at 16:18
• It cannot be done without array or array type formula, vba, or using evaluate in the name manager. – Scott Craner May 22 '19 at 17:37