在 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)、或 ，Minute(s) 它将与该单词前面的数字进行适当的乘法运算。然后将结果相加。