Общая формула
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Резюме
Чтобы разделить текст и числа, вы можете использовать формулу, основанную на функции НАЙТИ, функции МИН и функции ДЛСТР с функцией ВЛЕВО или ВПРАВО, в зависимости от того, хотите ли вы извлечь текст или число. В показанном примере формула C5 имеет следующий вид:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),B5&"0123456789"))
который возвращает 7, позицию числа 3 в строке "apples30".
Объяснение
Обзор
Формула выглядит сложной, но на самом деле механика довольно проста.
Как и в случае с большинством формул, которые разделяют или извлекают текст, ключевым моментом является определение местоположения того, что вы ищете. Когда у вас есть позиция, вы можете использовать другие функции для извлечения того, что вам нужно.
В этом случае мы предполагаем, что числа и текст объединены, и что число появляется после текста. Из исходного текста, который отображается в одной ячейке, вы хотите разделить текст и числа на отдельные ячейки, например:
| Оригинал | Текст | Число |
| Яблоки30 | Яблоки | 30 |
| персики24 | персики | 24 |
| апельсины12 | апельсины | 12 |
| персики0 | персики | 0 |
Как указано выше, ключ в этом случае - найти начальную позицию числа, что можно сделать с помощью такой формулы:
=MIN(FIND((0,1,2,3,4,5,6,7,8,9),A1&"0123456789"))
Когда у вас есть позиция, чтобы извлечь только текст, используйте:
=LEFT(A1,position-1)
И, чтобы извлечь только число, используйте:
=RIGHT(A1,LEN(A1)-position+1)
В первой формуле выше мы используем функцию НАЙТИ, чтобы найти начальную позицию числа. Для find_text мы используем константу массива (0,1,2,3,4,5,6,7,8,9), это заставляет функцию FIND выполнять отдельный поиск для каждого значения в константе массива. Поскольку константа массива содержит 10 чисел, результатом будет массив с 10 значениями. Например, если исходный текст - «яблоки30», результирующий массив будет:
(8,10,11,7,13,14,15,16,17,18)
Каждое число в этом массиве представляет позицию элемента в константе массива внутри исходного текста.
Затем функция MIN возвращает наименьшее значение в списке, которое соответствует позиции первого числа в исходном тексте. По сути, функция НАЙТИ получает все числовые позиции, а MIN дает нам первую числовую позицию: обратите внимание, что 7 - это наименьшее значение в массиве, которое соответствует позиции числа 3 в исходном тексте.
Вам может быть интересно узнать о странной конструкции для within_text в функции поиска:
B5&"0123456789"
Эта часть формулы объединяет все возможные числа от 0 до 9 с исходным текстом в B5. К сожалению, FIND не возвращает ноль, если значение не найдено, поэтому это просто умный способ избежать ошибок, которые могут возникнуть, когда число не найдено.
В этом примере, поскольку мы предполагаем, что число всегда будет вторым в исходном тексте, это работает хорошо, потому что MIN принудительно возвращает только наименьшее или первое вхождение числа. Пока число присутствует в исходном тексте, будет возвращена эта позиция.
Если исходный текст не содержит цифр, будет возвращена "фиктивная" позиция, равная длине исходного текста + 1. С этой фиктивной позицией приведенная выше формула LEFT по-прежнему будет возвращать текст, а формула RIGHT вернет пустую строку ("").
