Извлечь числа из текста ячеек в Excel

Как удалить из текстовых ячеек все символы, кроме цифр, таким образом «вытащив» из них цифры? Есть несколько простых и непростых решений.

Заменой остальных символов

Самым неоптимальным способом стала бы последовательная замена всех символов на «пустоту». Если лишних символов немного и вы умеете быстро печатать, можно обойтись и таким образом :) Но статья, конечно же, не о таких кейсах. Если данных тысячи строк и лишних символов много, такой подход приведет к трате огромного количества времени.

Если данных сотни тысяч строк, и известно, что цифры присутствуют лишь в малой их части, будет полезным сперва найти числа в ячейках. Это позволит отфильтровать попадающие под требования ячейки и далее работать уже с ними — так будет менее ресурсозатратно. Возможно, далее вам потребуется удалить эти цифры из текста в ячейках Excel.

Извлечение цифр из ячейки формулой

Такая формула массива будет работать только в сборках Excel, в которых поддерживается функция СЦЕП (аналог JOIN в Google Spreadsheets) – это некоторые сборки Excel 2016, 2019, и все релизы Excel 2021. Отличие СЦЕП от СЦЕПИТЬ в том, что она может принимать на вход диапазоны и массивы и возвращать при этом единое значение.

Так будет выглядеть формула, извлекающая только цифры из ячейки A1:

=СЦЕП(ЕСЛИОШИБКА(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)+0;""))

Механика её работы:

1. Сначала измеряется длина текстового значения ячейки функцией ДЛСТР
2. Далее создается текстовое выражение диапазона строк с помощью конкатенации амперсандом (&)
3. Это текстовое выражение преобразуется в реальный диапазон функцией ДВССЫЛ
4. Функция СТРОКА возвращает массив чисел от 1 до числа, соответствующего концу этого диапазона
5. Функция ПСТР извлекает по этому массиву чисел соответствующие символы по порядку
6. К каждому символу прибавляется 0 – это позволяет отличить цифры от остальных символов, т.к. цифры не вернут ошибку, а все остальные вернут
7. Функция ЕСЛИОШИБКА заменяет все ошибки на пустые строки
8. В финале функция СЦЕП объединяет массив в единую строку

Извлечение чисел с дробной частью (после точки или запятой)

К сожалению, формула выше не подходит для извлечения чисел, в которых есть разделитель разрядов (запятая или точка).

Для таких ситуаций формулу придётся усложнить:

=СЦЕП(ЕСЛИ(ЗНАК(НЕ(ЕОШИБКА(ПСТР(A1;ПОСЛЕД(ДЛСТР(A1));1)+0)))+ЗНАК(ПСТР(A1;ПОСЛЕД(ДЛСТР(A1));1)=",");ПСТР(A1;ПОСЛЕД(ДЛСТР(A1));1);""))

Она предполагает, что ваш разделитель разрядов – запятая. Думаю, догадаетесь, как изменить формулу, чтобы вместо запятой она извлекала числа с точками.

Формула требует одну из последних версий Excel, в которых есть функции ПОСЛЕД и СЦЕП.

С помощью пользовательских функций (UDF)

Пример пользовательской функции, которая поможет с извлечением чисел из текста:

Function extrNum(x As String) As Long   
   For n = 1 To Len(x)   
       If Mid(x, n, 1) Like "#" Then extrNum = extrNum & Mid(x, n, 1)   
   Next n   
End Function

Как применить данный код:

1. внедрить его в новый модуль книги (используемой сейчас или в личной книге макросов),
2. применить функцию на листе в подобном виде: =extrNum(A1).

С помощью синтаксиса регулярных выражений

Регулярные выражения — отличный помощник при работе с текстовыми данными. Их синтаксис для обработки простых паттернов довольно прост, но возможностей все усложнить предостаточно.

Как в платной, так и в бесплатной версии !SEMTools есть функции:

• regexReplace, берущая на вход 3 аргумента — строку с данными, текст регулярного выражения и строку для замены;
• regexExtract с двумя обязательными аргументами — строкой и паттерном для извлечения.

Удалить все символы кроме цифр (RegEx)

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

=REGEXREPLACE(A1;"[^\d]";"")

Взять первое число в ячейке (RegEx)

Выражение для извлечения первой сплошной последовательности цифр:

=REGEXEXTRACT(A1;"\d+")

Что потребуется:

1. установить !SEMTools,
2. применить функции.

Извлечь числа с плавающей запятой

Для извлечения чисел с плавающей запятой (включая целые) вместе с единицами измерения (например, “л”, “литр”, “литров”) из текста в Excel также можно использовать регулярные выражения через функцию REGEXEXTRACT.

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

Например, для строки “Сок 1.5 л апельсиновый” нужно извлечь “1.5 л”. Шаблон регулярного выражения должен учитывать:

• Числа с плавающей точкой (например, 0.5, 1.0, 2.75).
• Возможное наличие целой части и дробной части (после точки).
• Единицы измерения (например, “л”, “мл”), которые могут следовать сразу после числа или через пробел.

базовый шаблон:

\d+\.\d+\sл

Более надёжный, если единицы могут быть разные:

\d*\.?\d+\s*м*л(итр(ов|а)?)?(\s|$)

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

Возможно, после этого вам понадобится отделить текст от цифр в ячейках.

Извлечь цифры из текста в 1 клик

Пользователям платной версии !SEMTools доступна возможность извлекать цифры моментально прямо “на месте”, не прописывая никаких функций.

Макрос находится в группе «ИЗВЛЕЧЬ» в меню “Извлечь символы“.

Видеоинструкция

Удалить текст, а цифры оставить — именно такова механика алгоритма !SEMTools. Если между цифрами были пробелы или другие нецифровые символы, на всякий случай, вместо них остается один пробел. Будет полезно, если в ячейках могут присутствовать несколько чисел, и важно, чтобы они не склеились в одно число. Смотрите пример на видео ниже:

Если вам нужны вообще только цифры, их можно далее удалить заменой на пустоту с помощью процедуры “Найти и заменить“.

Эта статья также доступна на EN