Найти числа в текстовых ячейках

Проверить, есть ли в ячейке Excel цифры, можно формулой =СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(СТРОКА($1:$10)-1;A2)))>0: СТРОКА($1:$10)-1 даёт цифры от 0 до 9, ПОИСК ищет каждую в тексте, и если найдена хотя бы одна — формула возвращает ИСТИНА. Является ли значение ячейки числом, покажет =ЕЧИСЛО(A2), а найти конкретное число на листе — Ctrl+F. Отметить все ячейки с цифрами без формул можно процедурой !SEMTools.

Периодически возникает ситуация, когда нужно найти в диапазоне ячеек числа, но они могут быть вперемешку с текстом, что сильно затрудняет такой поиск.

Прежде всего, определимся с понятиями. Обнаружить в общем случае это выявить, существует или не существует искомое значение в ячейке. Результатом срабатывания такой функции будет булевое значение — ИСТИНА или ЛОЖЬ.

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

• извлечь цифры из текста в Excel;
• удалить цифры из ячеек.

Цифры - это собирательное понятие, включающее в себя целый десяток символов. Чтобы обнаружить, содержит ли ячейка цифры, нужно сделать 10 проверок — по 1 на каждую цифру — 0, 1, 2, 3, 4, 5, 6, 7, 8, 9.

Один из примеров такого "подхода в лоб" использован в описании функции ЕСЛИ на этом сайте.

Но есть ли способ заменить 10 проверок 1 проверкой, которая включала бы их все?  

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

Формула для определения цифр в ячейке

Формула ниже вернёт ИСТИНА или ЛОЖЬ, не самый простой и запоминающийся синтаксис, однако отлично справляется с задачей.

=СЧЁТ(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1))>0

Вариант без константы массива, который работает в любой версии и не требует Ctrl+Shift+Enter:

=СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(СТРОКА($1:$10)-1;A1)))>0

Синтаксис функции тот же, что и для поиска кириллицы или латиницы в тексте.

Найти несколько чисел формулой

Например, нужно найти чётные цифры, или найти в тексте все года от 2010 до 2025.

Под такие задачи можно адаптировать сложные формулы массива, использующие функции СЧЁТ и ПОИСК, выглядеть будут примерно так:

Ищем чётные цифры:

=СЧЁТ(ПОИСК({2:4:6:8:0};A1))>0

Ищем числовые последовательности любой длины (числа с ведущим нулём берём в кавычки — иначе «01» превратится в 1 и формула будет искать просто единицу):

=СЧЁТ(ПОИСК({"01":"02":"03":"911":"112"};A1))>0

Найти число на листе через Ctrl+F

Чтобы найти конкретное число в таблице, нажмите Ctrl+F, введите его и нажмите «Найти все». Если число отображается в формате (например, «1 200,00 ₽»), а ищете вы «1200», в «Параметрах» выберите «Область поиска: формулы» — поиск пойдёт по хранимому значению. «Ячейка целиком» исключит числа, в которые искомое входит частью (2106 при поиске 210).

Макрос "Обнаружить цифры" в !SEMTools

Пожалуй, наиболее простое решение, доступное после подключения !SEMTools к Excel. Копируем исходный столбец, применяем макрос и получаем результат на месте.

Как в Excel из всех цифр найти только определённые нужные цифры

Чтобы найти только нужные цифры, например, только чётные или только нечётные, есть два способа — с помощью регулярных выражений и с помощью адаптации формулы выше.

Найти нужные цифры с помощью регулярных выражений в !SEMTools

Пользователям надстройки !SEMTools доступны возможности использования функций регулярных выражений. Синтаксис довольно прост — перечисляем в квадратных скобках любые нужные вам цифры и жмем ОК. С его же помощью можно найти латиницу или обнаружить заглавные буквы.

Более того, можно не только найти, но и извлечь числа из текста. Например, числа с дробной частью и без:

Найти определённые отдельные слова-числа в тексте

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

На примере ниже мы ищем 210 как отдельное слово, при этом пропуская числа, содержащие его (например, 2106, 2107, 2108...).

Преобразовать числа в текст

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

• Число прописью в Excel;
• Склонение чисел в Excel.

Почему цифры не находятся

• ЕЧИСЛО возвращает ЛОЖЬ для числа. Число сохранено как текст. Преобразуйте текст в число или используйте проверку на цифры.
• Формула с {1:2:3…} выдаёт ошибку. Разделитель элементов в константе массива зависит от языковых настроек. Используйте вариант с СТРОКА($1:$10)-1 — он не зависит от локали.
• Ctrl+F не находит число, которое видно на экране. Число отображается с форматом или округлением. Переключите «Область поиска» между «значения» и «формулы».
• Найдено «210» внутри «2106». Поиск по вхождению находит части чисел. Для точного совпадения ищите число как отдельное слово.

Поиск чисел в Excel — частые вопросы

Как проверить, есть ли в ячейке цифры?

Формулой =СУММПРОИЗВ(--ЕЧИСЛО(ПОИСК(СТРОКА($1:$10)-1;A1)))>0 или регулярным выражением =REGEXMATCH(A1;"\d").

Как определить, что в ячейке число?

Функцией =ЕЧИСЛО(A1). Текст с цифрами («2024 год») числом не считается.

Как найти число в таблице Excel?

Ctrl+F → введите число → «Найти все». Для точного совпадения включите «Ячейка целиком».

Как извлечь найденные числа?

Подробно — в статье как извлечь числа из текста.

Итоги: как найти числа в тексте

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