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

Убрать буквы из ячейки Excel и оставить только цифры в Excel 365 можно формулой =СЦЕП(ЕСЛИОШИБКА(--ПСТР(A2;ПОСЛЕД(ДЛСТР(A2));1);"")): она перебирает символы строки и склеивает только те, что превращаются в число. Тот же результат даёт регулярное выражение =REGEXREPLACE(A2;"[^\d]";"") — в Excel 365 это встроенная функция РЕГЗАМЕНИТЬ, в более ранних версиях она появляется вместе с бесплатной !SEMTools. Первое число из текста извлекает =REGEXEXTRACT(A2;"\d+"), а прямо в исходных ячейках цифры оставляет процедура !SEMTools в один клик.

Оставить цифры заменой остальных символов

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

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

Оставить в ячейке только цифры формулой

В Excel 365 и 2021 формула короткая: ПОСЛЕД создаёт номера символов от 1 до длины строки, ПСТР вынимает каждый символ, два минуса пытаются превратить его в число, ЕСЛИОШИБКА отбрасывает не-цифры, а СЦЕП склеивает оставшиеся:

В Excel 2019 функции ПОСЛЕД нет — номера символов создают через СТРОКА и ДВССЫЛ, а формулу вводят сочетанием Ctrl+Shift+Enter:

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

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

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

В Excel 2016 и раньше нет и СЦЕП — там помогут пользовательская функция или регулярные выражения ниже.

Извлечь число с дробной частью

Формула выше отбрасывает десятичную запятую, и «1,5» превращается в «15». Чтобы сохранить дробную часть, разрешаем формуле оставлять не только цифры, но и запятую (Excel 365):

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

Извлечь числа пользовательскими функциями (UDF)

В любой версии Excel, включая 2010–2016, цифры извлекает пользовательская функция на VBA. Она возвращает текст, поэтому ведущие нули сохраняются, а длинные номера не обрезаются:

Function extrNum(x As String) As String
    Dim n 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).

Извлечь числа регулярными выражениями

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

В Excel 365 есть встроенные РЕГЗАМЕНИТЬ (REGEXREPLACE) и РЕГИЗВЛЕЧЬ (REGEXEXTRACT). В более ранних версиях такие функции есть в платной и бесплатной версии !SEMTools:

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

Удалить все символы, кроме цифр, регуляркой

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

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

Взять первое число в ячейке регуляркой

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

=REGEXEXTRACT(A1;"\d+")

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

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

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

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

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

Базовая формула:

=REGEXEXTRACT(A1;"\d+\.\d+\sл")

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

=REGEXEXTRACT(A1;"\d*\.?\d+\s*м*л(итр(ов|а)?)?(\s|$)")

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

Для русской записи с запятой замените в шаблоне «\.» на «,» или на «[.,]», чтобы подходили оба разделителя. Возможно, после этого вам понадобится отделить текст от цифр в ячейках.

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

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

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

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

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

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

Почему извлечённые цифры работают не так, как ожидалось

• Числа склеились. Формулы «только цифры» объединяют все цифры строки: «№1024 от 05.10» превратится в «10240510». Если чисел несколько, извлекайте первое через REGEXEXTRACT или разделяйте их пробелом.
• Результат не суммируется. СЦЕП, REGEXREPLACE и пользовательская функция возвращают текст. Чтобы получить число, добавьте два минуса: =--REGEXREPLACE(A2;"[^\d]";""). Но для артикулов и телефонов оставляйте текст — иначе пропадут ведущие нули.
• Ошибка #ИМЯ?. В вашей версии Excel нет функции из формулы: ПОСЛЕД и РЕГЗАМЕНИТЬ — только в Excel 365, СЦЕП — с Excel 2019. Используйте вариант для своей версии или функции !SEMTools.
• Длинный номер превратился в 7,91235E+10. Excel показывает большие числа в экспоненциальном формате, а после 15 цифр теряет точность. Храните номера карт, счетов и телефонов как текст.

Извлечь цифры из ячейки — частые вопросы

Как убрать буквы из ячейки и оставить цифры?

В Excel 365 — формулой =СЦЕП(ЕСЛИОШИБКА(--ПСТР(A2;ПОСЛЕД(ДЛСТР(A2));1);"")) или =РЕГЗАМЕНИТЬ(A2;"[^\d]";""). В других версиях — пользовательской функцией VBA или функцией REGEXREPLACE из !SEMTools.

Как извлечь первое число из текста?

Регулярным выражением: =REGEXEXTRACT(A2;"\d+"). Для числа с дробной частью — =REGEXEXTRACT(A2;"\d+([.,]\d+)?").

Как оставить только цифры без формул?

Процедурой !SEMTools «Извлечь цифры»: она меняет данные прямо в ячейках и ставит пробел между отдельными числами, чтобы они не склеились.

Как превратить извлечённые цифры в число?

Добавьте перед формулой два минуса: =--REGEXREPLACE(A2;"[^\d]";"").

Итоги: как извлечь цифры из текста в Excel

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

Вас наверняка заинтересуют и обратные задачи: удалить цифры из текста и найти ячейки с числами.