Общая формула
=MAX(ISNUMBER(VALUE(MID(A1,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Резюме
Чтобы отделить число от значения единицы, вы можете использовать формулу, основанную на нескольких функциях: MAX, ISNUMBER, VALUE и MID. В показанном примере формула C5 имеет следующий вид:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Примечание: это экспериментальная формула, в которой используется жестко закодированная константа массива, приведенная здесь для справки и комментариев. Только случайно протестировано, поэтому будьте осторожны, если вы используете или адаптируете.
Объяснение
Иногда вы сталкиваетесь с данными, в которых единицы измерения напрямую смешиваются с числами (например, 8 км, 12 вольт, 7,5 часов). К сожалению, Excel будет обрабатывать числа в этом формате как текст, и вы не сможете выполнять математические операции с такими значениями.
Чтобы отделить число от стоимости единицы, вам необходимо определить позицию последнего числа. Если вы добавите 1 к этой позиции, у вас будет начало текста единицы. Эта формула использует эту концепцию, чтобы выяснить, где начинается единица измерения.
В показанном примере формула C5 имеет следующий вид:
=MAX(ISNUMBER(VALUE(MID(B5,(1,2,3,4,5,6,7,8,9),1)))*(1,2,3,4,5,6,7,8,9))+1
Эта формула использует функцию MID для извлечения первых 9 значений в B5, по одному символу за раз. Результатом является такой массив:
("8","0","v","","","","","","")
Затем мы используем функцию VALUE для преобразования чисел в текстовом формате в фактические числа. Результат:
(8,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!)
Мы запускаем этот массив через ISNUMBER, чтобы получить:
(TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE)
Затем умножьте это на другой массив с 9 числами, чтобы получить:
(1,2,0,0,0,0,0,0,0)
Затем мы используем MAX, чтобы получить наибольшее значение, которое является позицией «последнего числа».
Наконец, мы добавляем 1 к позиции, чтобы получить "начальную позицию".
Наконец, мы используем эту позицию со стандартными функциями LEFT и RIGHT, чтобы отделить числа от единиц:
=VALUE(LEFT(B5,C5-1)) // number =TRIM(RIGHT(B5,LEN(B5)-C5+1)) // unit
Обратите внимание, что константа жестко запрограммированного числового массива является уловкой для удобства и обрабатывает только необработанные значения длиной до 9 символов.
