在 Excel 2016/O365 中計算字串公式的更好方法？

Question

目標：將「2 天 16 小時 30 分鐘」評估為秒。

我在網上搜尋並沒有找到合適的答案，我想要一個 100% 的公式解決方案。限制：無 VBA、避免名稱管理器、僅日/小時/分鐘、存在空格。

我正在工作的當前解決方案：

Answer 1

將此 SUM 用作數組公式：

=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))
)

作為陣列公式，退出編輯模式時必須使用 Ctrl-Shift-Enter 確認，而不是 Enter。

這會迭代字串中的每個單字。如果它找到Day(s), Hour(s),OrMinute(s) 它會與該單字前面的數字進行正確的乘法。然後它添加結果。