Обнаружить текстовые символы в ячейках Excel

Проверить, содержит ли ячейка определённый текст, можно формулой =ЕСЛИ(ЕЧИСЛО(ПОИСК("текст";A2));"да";"нет"): ПОИСК ищет фрагмент без учёта регистра, ЕЧИСЛО превращает результат в ИСТИНА/ЛОЖЬ, а ЕСЛИ выводит нужный ответ. Есть ли в ячейке хоть какие-то буквы, покажет =НЕ(СОВПАД(СТРОЧН(A2);ПРОПИСН(A2))), а функция ЕТЕКСТ проверяет только тип значения — текст или нет. Ограничить длину текста поможет =ДЛСТР(A2)<=50. Отметить все ячейки с буквами и цифрами без формул можно процедурой !SEMTools в 2 клика.

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

Самым простым вариантом для такой фильтрации было бы получить напротив каждой ячейки списка ответ на вопрос в булевом формате да/нет (в Excel ИСТИНА/ЛОЖЬ) — содержит ли ячейка текст.

Если ячейка содержит текст — формула

Самый частый вопрос — как вывести результат в зависимости от того, есть ли в ячейке нужное слово или фрагмент. ПОИСК возвращает позицию фрагмента или ошибку, если его нет, поэтому её оборачивают в ЕЧИСЛО:

=ЕСЛИ(ЕЧИСЛО(ПОИСК("текст";A2));"да";"нет")

Тот же результат даёт =ЕСЛИ(СЧЁТЕСЛИ(A2;"*текст*");"да";"нет") — звёздочки означают «любые символы до и после». Если важен регистр, замените ПОИСК на НАЙТИ. Искать целое слово, а не фрагмент, помогают пробелы вокруг: подробно — в статье поиск по слову в ячейках.

Если ячейка содержит не более N символов

Длину текста считает ДЛСТР: =ЕСЛИ(ДЛСТР(A2)<=50;"ок";"длинно") — удобно для заголовков объявлений и мета-тегов. Чтобы запретить ввод длинного текста, используйте «Данные → Проверка данных → Длина текста → меньше или равно». Подробнее — как посчитать символы в ячейке.

Проверить ячейку на наличие букв: ЕТЕКСТ и СОВПАД

В Excel проверка ячейки на наличие текста присутствует в виде функции ЕТЕКСТ, но она довольно бестолковая по своей сути. Все, что проверяет функция, — это в каком формате находится содержимое ячейки. Например, в ячейке может быть пробел, число в текстовом формате или пунктуационный символ — функция во всех случаях вернет ИСТИНА. Если вас устраивает такой результат, дальше можно не читать.

Но наверняка многие, когда ищут, как проверить ячейку на наличие текста, имеют в виду самый настоящий текст, ключевым признаком которого является сама текстовая информация, а не формат ячейки.

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

Для одних текст — это исключительно буквы, буквенные символы.

Если вы в их числе, формула проверки довольно проста. Функция СОВПАД, точнее, отрицание её результата функцией НЕ поможет нам вычислить наличие букв в тексте, а помогут ей в этом функции СТРОЧН и ПРОПИСН:

=НЕ(СОВПАД(СТРОЧН(A1);ПРОПИСН(A1)))

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

Найти буквы и цифры в ячейке одновременно

Однако кто-то может считать текстом ещё и цифровые символы или попросту цифры (из них ещё составляются числа - не путаем понятия :)).

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

Макрос легко найти в первой группе под названием "НАЙТИ" в меню "Символы".

А регулярное выражение для поиска кириллицы, латиницы и цифр в ячейке выглядит так:

[A-Za-zА-Яа-яЁё0-9]

На листе этот шаблон применяется функцией =REGEXMATCH(A2;"[A-Za-zА-Яа-яЁё0-9]") (в Excel 365 — встроенная РЕГТЕСТ). Буква «ё» добавлена отдельно: диапазон А-я её не включает.

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

Для случаев, когда мы хотим найти не просто текст, а определённый текст, например, кириллицу или латиницу или цифры по отдельности — есть отдельные процедуры:

• Найти кириллицу;
• Найти латиницу;
• Найти цифры.

Для ещё более сложных случаев можно использовать процедуру "Найти ячейки по регулярному выражению" в той же группе "НАЙТИ".

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

Найти текстовые символы в 2 клика

Краткая запись действий для обладателей !SEMTools:

1. выделяем исходный диапазон и копируем в столбец рядом,
2. выделяем диапазон ячеек,
3. находим на панели !SEMTools группу «Обнаружить»,
4. открываем меню «Символы»,
5. кликаем «Буквы-цифры».

Почему проверка на текст ошибается

• ЕТЕКСТ возвращает ИСТИНА для цифр. Число сохранено как текст. ЕТЕКСТ проверяет тип значения, а не наличие букв. Для букв используйте СОВПАД или преобразуйте текст в числа.
• Формула ЕСЛИ(ПОИСК(…)) выдаёт #ЗНАЧ!. Фрагмент не найден, и ПОИСК вернул ошибку. Оберните его в ЕЧИСЛО.
• Ячейка выглядит пустой, но проверка находит текст. В ней пробел или пустая строка из формулы ="". Проверьте ДЛСТР(A2) — у пустой ячейки 0.
• СЧЁТЕСЛИ не находит текст с «?» или «*». Это подстановочные знаки. Чтобы искать сам символ, поставьте перед ним тильду: "*~?*".

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

Как написать формулу «если ячейка содержит текст, то…»?

=ЕСЛИ(ЕЧИСЛО(ПОИСК("текст";A2));"да";"нет") — замените «текст» на нужный фрагмент, а «да»/«нет» — на нужные значения.

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

Для фрагмента — ПОИСК, как выше. Для целого слова — =ЕЧИСЛО(ПОИСК(" слово ";" "&A2&" ")).

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

Функциями =ЕТЕКСТ(A2) и =ЕЧИСЛО(A2). Текст по умолчанию выравнивается по левому краю, числа — по правому.

Как проверить, что в ячейке не более N символов?

Формулой =ДЛСТР(A2)<=50 или ограничить ввод через «Проверку данных» по длине текста.

Итоги: как проверить ячейку на текст в Excel

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