Вычислить буквенно-цифровую строку в целое число в Excel

У меня есть проблема, которую я не смог понять даже со многими идеями, представленными в других сообщениях. Мои данные поступают в Excel, и здесь приведены примеры каждого способа, которым могут обладать данные в данной ячейке:

4days 4hrs 41mins 29seconds 23hrs 43mins 4seconds 2hrs 2mins 52mins 16seconds 

Конечным результатом было бы вычисление общих минут, позволяя секундам быть проигнорированным, чтобы предыдущие значения закончились следующим образом:

 6041 52 1423 122 

Кто-нибудь может подумать, как это сделать?

Спасибо за помощь!

Бит утомительный (и предполагает, что единицы всегда множественные) также дает результаты в другом порядке, например), но только с формулами, если ваши данные находятся в столбце A, в B1 и скопированы:

 ="="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"days","*1440+"),"hrs","*60+"),"mins","*1+"),"seconds","*0")," ","")&0 

затем скопируйте B и вставьте специальные значения в C и примените текст к столбцам к C с вкладкой в ​​качестве разделителя.

Эта формула массива ** также должна работать:

= SUM (IFERROR (0 + MID (REPT ("", 31) & SUBSTITUTE (A1 & "dayhrminsecond", "", REPT ("", 31)), FIND ({"day", "hr", "min" «второй»}, REPT («», 31) & SUBSTITUTE (A1 & «dayhrminsecond», «», REPT («», 31))) – 31,31), 0) * {1440,60,1,0})

С уважением

** Формулы массива не вводятся так же, как «стандартные» формулы. Вместо нажатия только ENTER, сначала удерживайте CTRL и SHIFT, а затем нажмите ENTER. Если вы сделали это правильно, вы заметите, что Excel помещает фигурные скобки {} вокруг формулы (хотя не пытайтесь вручную вставить их самостоятельно).

Самый простой вариант – это, вероятно, VBA с регулярным выражением. Затем вы можете легко найти каждое из полей и выполнить математику.

Если вы хотите придерживаться «чистого» Excel, то кажется, что только вариант – использовать SEARCH или FIND чтобы найти положение каждого из «дней», «hrs», «mins» в тексте (возможно, вам придется проверьте, всегда ли они во множественном числе). Затем используйте MID с указанной выше позицией для извлечения разных компонентов. См. http://office.microsoft.com/en-gb/excel-help/split-text-among-columns-by-using-functions-HA010102341.aspx для аналогичных примеров.

Но для обработки тех случаев, когда некоторые компоненты отсутствуют, есть довольно много работы, поэтому либо вы будете использовать довольно много ячеек, так что вы получите очень сложную формулу …

Вот пользовательская функция, написанная в VBA, которая берет вашу строку в качестве аргумента и возвращает количество минут. Проверяются только первые символы имен интервалов времени (например, d, h, m), поскольку это, по-видимому, обеспечивает достаточную дискриминацию.

Чтобы ввести эту пользовательскую функцию (UDF), открывается редактор Visual Basic. Убедитесь, что ваш проект выделен в окне Project Explorer. Затем в верхнем меню выберите «Вставить / Модуль» и вставьте код ниже в открывшееся окно.

Чтобы использовать эту пользовательскую функцию (UDF), введите формулу типа

= SumMinutes (А1)

в некоторой ячейке.

 Option Explicit Function SumMinutes(S As String) As Long Dim RE As Object, MC As Object Dim lMins As Long Dim I As Long Set RE = CreateObject("vbscript.regexp") With RE .Pattern = "(\d+)(?=\s*d)|(\d+)(?=\s*h)|(\d+)(?=\s*m)" .Global = True .ignorecase = True If .test(S) = True Then Set MC = .Execute(S) For I = 0 To MC.Count - 1 With MC(I) lMins = lMins + _ .submatches(0) * 1440 + _ .submatches(1) * 60 + _ .submatches(2) End With Next I End If End With SumMinutes = lMins End Function 
  • Формат Excel VBA как десятичный с Combobox
  • Чтение значения времени excel с использованием API OpenXML
  • Excel Powerpivot Dax (не native excel) - преобразование времени в десятичные часы
  • Как импортировать собственные значения формата времени из Excel в R?
  • Запись даты из Excel VBA на рабочий лист дает неправильное значение
  • Excel VBA: если время находится между двумя моментами, добавьте текст в строку, а не пустую
  • MySQL для Excel TIME Column
  • Преобразование ТЕКСТА, представляющее значение НЕГАТИВНОГО ВРЕМЕНИ, к числу или времени для добавления (Excel)
  • Формат Excel, столбец для получения суммы hh: mm: ss, когда они перечислены таким образом
  • Как я могу отформатировать время в excel с 2017-02-17 01:59:57 UTC?
  • Преобразование строки, содержащей дни, часы и минуты в hh: mm: ss
  • Interesting Posts

    Подсчитать количество различных ячеек в VBA

    WebAPI и угловой файл JS Excel – файл поврежден

    заполнение формы PDF с данными Excel

    Как подсчитать количество студентов в разных условиях на листе посещаемости

    Excel VBA: для каждого цикла для длинного диапазона

    Запустить новое окно браузера (небольшое) внутри Excel

    Проверьте имена листов, чтобы избежать дублирования

    Apache POI: Почему исключение при копировании одного и того же файла excel?

    Excel Vba – Как получить имя листа из Application.Caller

    Код не работает во всех открытых книгах

    Код слишком медленный, чтобы классифицировать и копировать всего 250 строк

    xlsx excel generation – никогда не видел этого раньше

    Использование функции разделения Excel для разделения содержимого ячеек на несколько столбцов без равномерного разделителя

    Не удается завершить мой цикл

    Скопируйте строку на другой лист в Excel с помощью VBA

    Давайте будем гением компьютера.