Как выбрать первый номер в ячейке в Excel?

Question

У меня есть таблица, в которой хранится как число, так и некоторый текст в ячейке, и я хочу выполнить арифметическую операцию на основе чисел. Итак, мой стол выглядит так:

Number1       | Number2       | Product
2             | 3             | 6
3 (some text) | 40            | 120
4             | 5 (here too)  | 20

Таким образом, при наличии этих уродливых текстов, я все еще хочу иметь возможность производить колонку 3 автоматически. Я думаю, мне нужна функция, которая анализирует подстроку до первого нечислового символа в число. Могу ли я сделать это с помощью Excel?

PS: номера могут иметь более 1 цифры. Можно с уверенностью предположить, что они являются целыми числами, но было бы неплохо увидеть решение, которое применимо и к плавающим точкам :)

Спасибо,

Answer 1

Чтобы получить числа, если они находятся в начале строки, мы можем использовать это:

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

как наша базовая формула, это найдет конец числа и вернет его как конец функции MID().

Здесь много чего происходит:

SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))) Поскольку эта часть перебирает числа, она заменяет последний экземпляр каждого числа на }}} .

Третий критерий ЗАМЕНЫ - это экземпляр. Мы находим количество экземпляров с LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")) . Он перебирает числа и заменяет каждое из них ничем. Затем он находит разницу в длине исходной строки и новой.

Таким образом, в случае A2, когда он повторяется до 2 он находит 2, а внешняя замена заменяет последнюю на }}} . Это просто временный заполнитель.

Агрегатная функция является многофункциональной функцией. 14 сообщает функции, которые мы используем для функции Large() . 6 говорит функции игнорировать ошибки. Это важно, так как многие из итераций ничего не найдут и вернут ошибку.

С 1 в конце он сообщает функции, что мы хотим получить максимальную отдачу от функции поиска, которая ищет те временные }}} , которые помещаются в ходе итерации в последний экземпляр каждого числа.

Таким образом, Агрегат возвращает максимальное найденное число. Который мы переходим к критерию длины в функции Mid.

Итак, теперь мы нашли число в начале строки.

Таким образом, мы можем умножить два из них вместе, чтобы получить желаемый результат (любая математическая функция превратит возвращаемую строку в число):

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

Одна оговорка Функция агрегирования была введена в Excel 2010. Может не работать со старыми версиями.

Если у вас более старая версия, вам нужно использовать эту более длинную формулу:

=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))

Он работает примерно так же, как приведенный выше, он должен сначала проверить наличие ошибок, прежде чем найти макс.

Answer 2

Предполагая, что число всегда отделяется от любого текста пробелом, вы можете использовать что-то вроде этого. Скажем, в вашем примере данные начинаются с A2, поэтому ваш первый результат в C2 обычно будет выглядеть так:

=A2*B2

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

=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))

Если исходная ячейка не является числом, она находит первый пробел и возвращает символы перед ним. Технически, он возвращает текстовое значение, содержащее цифры, но Excel достаточно умен, чтобы рассматривать его как число при расчете продукта (нет необходимости вкладывать его в другую функцию, чтобы преобразовать его в число). И обратите внимание, что если число является плавающей точкой, это все равно будет работать.

Предостережение: это основывается на "чистых" исходных данных, т. Е. Либо на числовой записи, либо на текстовой записи, где она начинается с числа, за которым следует пробел. Если есть ячейка, которая не соответствует этому, вам необходимо либо очистить данные, либо включить некоторую форму проверки ошибок. Лучшим решением было бы разделить два столбца данных так, чтобы любой текст находился в отдельной ячейке.