Мне нужно получить время из текста в ячейке Excel. Текст содержит номер билета, название билета, журнал времени и процент. Все они находятся в одной ячейке. Смотрите пример ниже:
12345678: The bottom part is incorrect 0:15h 0%
Мне нужно получить 0:15. Я пытался использовать комбинацию функций LEFT и RIGHT, но она сработает только если процент из последнего предложения будет состоять только из 1 цифры. Процент варьируется от 0% до 100%. Длина названия билета также варьируется.
решение1
Я использую бесплатноreg-ex заменить надстройку* который добавляет в Excel функциональность регулярных выражений, с помощью которой вы можете использовать следующую формулу:
=RegExFind(A2,"\d{1,2}:\d{1,2}")
* Я не связан с этой надстройкой
решение2
Ответ Máté Juhász, безусловно, самый простой. Однако, если вы не хотите получать дополнение и не знакомы с регулярными выражениями (и не хотите учиться), есть более запутанный способ получить результат с помощью встроенных функций. Его можно немного упростить, если использовать вспомогательные столбцы, чтобы избежать повторений. Я воспользуюсь этим, чтобы объяснить, как это работает.
Все «слова» разделены пробелами. Название билета может содержать разное количество слов. Последний элемент, процент, находится после последнего пробела, а время — после предпоследнего пробела. Столбец B вычисляет количество пробелов по этой формуле:
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
Эта формула работает, удаляя все пробелы, а затем вычисляя, на сколько символов сократилась строка. В примере 7 пробелов.
Столбец C отслаивает конец строки, начиная с времени. Формула:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),B1-1))+1,LEN(A1))
Это работает путем замены произвольного целевого символа, который не встречается в данных (в данном случае CHAR(1)), на пробел перед временем (т. е. предпоследний пробел) с использованием функции SUBSTITUTE, которая позволяет указать конкретный экземпляр для замены. Затем FIND находит позицию этого символа, а MID извлекает остальную часть строки, начиная со следующей позиции.
Результат в столбце D извлекает время по следующей формуле:
=LEFT(C1,FIND("h",C1)-1)
Он работает путем поиска буквы «h» и извлечения всего, что ей предшествует.
Вы можете сделать это в одной формуле без использования вспомогательных столбцов. Если вы замените ссылки на вспомогательные ячейки на формулы вспомогательных ячеек, эта формула будет выглядеть так:
=LEFT(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,LEN(A1)),FIND("h",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))+1,LEN(A1)))-1)
Обратите внимание, что все это предполагает чистые данные без конечных пробелов. Если конечные пробелы возможны, оберните все ссылки на A1 с помощью TRIM() или обрежьте A1 во вспомогательном столбце и ссылайтесь на него, чтобы избежать повторения.
решение3
Попробуйте следующую формулу:
=TRIM(RIGHT(TRIM(LEFT(SUBSTITUTE(TRIM(E10);"h ";REPT(" ";99));99));5))