Загальна формула
=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. У наведеному прикладі формула в С5 має вигляд:
=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 до цієї позиції, у вас буде початок одиничного тексту. Ця формула використовує цю концепцію, щоб з’ясувати, з чого починається одиниця виміру.
У наведеному прикладі формула в С5 має вигляд:
=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 до позиції, щоб отримати позицію "початок одиниці".
Нарешті, ми використовуємо цю позицію зі стандартними функціями ВЛІВО та ВПРАВО для відокремлення чисел від одиниць:
=VALUE(LEFT(B5,C5-1)) // number =TRIM(RIGHT(B5,LEN(B5)-C5+1)) // unit
Зауважте, що жорстко закодована константа масиву чисел є зручною для зручності і обробляє лише необроблені значення довжиною до 9 символів.
