Лучший способ вычисления строковой формулы в Excel 2016/O365?

Question

ЦЕЛЬ: оценить «2 дня 16 часов 30 минут» с точностью до секунд.

Я искал в интернете и не нашел подходящего ответа, я хочу 100% решение формулы. Ограничения: нет VBA, избегайте менеджера имен, только день/час/минута, есть пробелы.

Текущее решение, которое у меня работает:

Answer 1

Используйте эту СУММУ как формулу массива:

=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) Он выполняет правильное умножение с числом перед этим словом. Затем он складывает результаты.