Извлечение времени из текстовой строки в MS EXCEL

Зачастую можно встретить текстовые строки, которые содержат время, например 21ч. 19 мин. 1 сек. или 21 час 19 минут 30 секунд. Как правило, такие текстовые сроки генерируются различными программами, поэтому имеют строгую структуру-формат, что облегчает работу с ними. Извлечем из них значение времени в стандартном для MS EXCEL формате чч:мм:сс

На картинке выше в столбце В содержится текстовая строка, а правее столбец С содержащий значения в формате Время. Со значениями из правого столбца можно производить вычисления, а с текстовыми значениями этого делать, понятно, не получится. В этом и состоит основная причина данного преобразования.

Понятно, что время записать в текстовой форме можно по разному, например 5 ч. 3 мин. или 05 часов 03 минуты или 5ч03м и т.п. Чем проще формат, тем проще его преобразовать: формулы короче и проще или можно применить стандартный инструмент Текст-по-столбцам, например. Так как форматов бесконечно много, то универсальной формулы не существуют: все они работают для определенных шаблонов текстовых строк. Постараемся написать формулы для максимально широкого набора текстовых строк.

Напишем формулы для перевода следующего типа текстовой строки: 21ч. 19 мин. 1 сек. Формулы в файле примера будут работать также если строка содержит только минуты 59 мин. или только часы или только секунды или будет отсутствовать какой либо из этих компонентов (см. картинку выше, столбец В). Кроме того, единицы времени могут быть практически любыми: минут, мин., м, minutes. Единственное, они должны не иметь склонения. Также не имеет значения наличие пробелов в строке, а минуты/часы/секунды могут быть записаны как в формате с 0 так и без (07 сек или 7 сек).

В файле примера для формул создано несколько дополнительных столбцов, чтобы логика вычислений была более прозрачной:

• сначала ищется позиция единицы измерения часов =ПОИСК(B$7;$B14) Результатом может быть позиция единицы измерения часов или ошибка, когда строка не содержит часов. Это столбец Е
• затем определяется количество часов =ЕСЛИ(НЕ(ЕОШ(E14));ЛЕВСИМВ($B14;E14-1)&":";"0:"). Если часов нет, то формула возвращает текстовую строку "0:". Это столбец I
• исходная строка преобразуется (отсекаются часы) =ЕСЛИ(НЕ(ЕОШ(E14));ПРАВСИМВ(B14;ДЛСТР(B14)-E14-ДЛСТР($B$7)+1);B14) . Это столбец F
• аналогично обрабатываются минуты
• исходная строка преобразуется (отсекаются часы и минуты) =ЕСЛИ(НЕ(ЕОШ(G14));ПРАВСИМВ(F14;ДЛСТР(F14)-G14-ДЛСТР($B$8)+1);F14). Это столбец H
• от исходной строки остались только секунды. Удаляем единицы секунд =ПОДСТАВИТЬ(H14;$B$9;""). . Это столбец K
• используем замечательную функцию ВРЕМЗНАЧ(), которая убирает пробелы, преобразует текст в числовой (временной) формат, учитывая спецсимвол ":". Вот результирующая формула =ВРЕМЗНАЧ(I14&J14&K14)

Если в вашей строке используется вместо "сек." единица измерения "секунд" или "сек" (без точки), то просто откорректируйте ячейки В7:В9. Формулы продолжат работать. 

Если в исходных текстовых строках используются различные склонения: "секунд", "секунда", "секунды", то автозаменой (CTRL+F) уберите из исходных текстовых строк все слова "секунда" и "секунды", заменив на "секунд".