Извлекать только числа из текста (строки)
В Excel вы можете использовать формулы для извлечения числа из значения, состоящего из текста и чисел. В следующем примере мы использовали формулу для получения 1984 года из значения «TPS1984».
Формула для извлечения чисел из текста
=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))В этом уроке мы подробно разберем эту формулу. И чтобы это понять, нам придется разделить его на несколько частей:
- Во-первых, у нас есть функция LEN, позволяющая получить количество символов в значении.
- После этого INDIRECT использует ссылку на ячейку, используя цифры 1 и 7 (возвращенные LEN).
- Тогда функция СТРОКА будет использовать ДВССЫЛ и вернет массив чисел, начиная с 1 и заканчивая 7.
- Теперь MID будет использовать этот массив и возвращать новый со значениями ячейки, на которую вы ссылаетесь.
- Далее у нас есть простая формула умножения для умножения массива на 1. С помощью этого простого умножения вы получите новый массив, в котором будет #ЗНАЧ! Ошибка текстовых значений и чисел останется нетронутой.
- Отсюда функция ЕСЛИОШИБКА преобразует значения ошибок в пустые значения.
- И в конце TEXTJOIN объединит значения, и в результате у вас останутся только числа.
Приведенная выше формула работает только для версии Excel 2019 и выше. Это связано с тем, что мы используем TEXTJOIN, недоступный в более ранних версиях.
Если вам нужно значение для части формулы, выберите эту часть и нажмите горячую клавишу F9.
TEXTJOIN — это изменчивая функция, которая меняет значение при обновлении ячейки на листе. Это может немного замедлить работу вашей книги.
А что, если я использую более раннюю версию (2007, 2010, 2013, 2016)
Если вы используете более ранние версии, вам необходимо использовать другую формулу. Такая формула:
=IF(SUM(LEN(A1)-LEN(SUBSTITUTE(A1, {"0","1","2","3","4","5","6","7","8","9"}, "")))>0, SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("$1:$"&LEN(A1))),1))* ROW(INDIRECT("$1:$"&LEN(A1))),0), ROW(INDIRECT("$1:$"&LEN(A1))))+1,1) * 10^ROW(INDIRECT("$1:$"&LEN(A1)))/10),"") =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)Две приведенные выше формулы хорошо работают с разными типами значений. Я проверял это следующим образом.
В ячейке A1 выше у нас есть комплексное значение, состоящее из текста, чисел и символов, и в результате мы имеем только числа.