Функция ПОИСК в Excel находит позицию текста внутри ячейки без учёта регистра: =ПОИСК("диван";A2) вернёт номер символа, с которого начинается «диван», или ошибку #ЗНАЧ!, если его нет. Чтобы проверить, есть ли текст в ячейке, ПОИСК оборачивают в ЕЧИСЛО: =ЕЧИСЛО(ПОИСК("диван";A2)) даёт ИСТИНА или ЛОЖЬ. ПОИСК понимает подстановочные знаки ? и *, а с учётом регистра ищет функция НАЙТИ. Если нужно найти данные на листе, а не в формуле, нажмите Ctrl+F.
Эта статья о функции поиска текста внутри ячейки. Больше о поиске данных в Excel: Найти определенные данные в Excel.
| Раздел функций | Текстовые |
| Название и описание на английском | SEARCH |
| Волатильность | Не волатильная |
| Похожие функции | НАЙТИ (функция), поиск и замена (процедура) |
Что делает функция ПОИСК?
Эта функция аналогична функции НАЙТИ и так же ищет подстроку в строке. Когда искомое найдено, отображается его позиция в тексте в виде числа.
Отличие от функции НАЙТИ в том, что ПОИСК не принимает в расчет регистр текста. Как искомого, так и того, в котором мы ищем. Также она поддерживает подстановочные операторы.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Текст | ПОИСК "д?ван" | ПОИСК "~?" | ПОИСК "д" | НАЙТИ "д" |
| 2 | Диван? | 1=ПОИСК("д?ван";A2) | 6=ПОИСК("~?";A2) | 1=ПОИСК("д";A2) | #ЗНАЧ!=НАЙТИ("д";A2) |
| 3 | кровать | #ЗНАЧ!=ПОИСК("д?ван";A3) | #ЗНАЧ!=ПОИСК("~?";A3) | #ЗНАЧ!=ПОИСК("д";A3) | #ЗНАЧ!=НАЙТИ("д";A3) |
? — один любой символ, * — любое число символов. Чтобы найти сам знак вопроса, ставьте перед ним тильду: "~?". НАЙТИ различает регистр, поэтому строчную «д» в «Диван» не находит.
В английском Excel: =SEARCH("д?ван",A2)
У обеих функций есть процедура-аналог Найти и Заменить - как у процедуры, у нее есть свои преимущества и недостатки.
Синтаксис функции ПОИСК
=ПОИСК(ИскомыйТекст;СтрокаВКоторойИщем;[СтартоваяПозиция])
- ИскомыйТекст — символ или сочетание, которое ищем
- СтрокаВКоторойИщем — ячейка, текстовое значение или любое возвращаемое другой функцией выражение.
- Стартовая позиция — опциональный параметр, при отсутствии поиск происходит с первого символа
Если текст содержит более одного вхождения, возвращается позиция первого.
Третий (опциональный) параметр используется для поиска с определенной позиции в тексте и по умолчанию равен 1.
Если искомое не найдено в тексте, функция возвращает ошибку #ЗНАЧ.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Текст | Где «диван» | «а» с 5-го символа | Где «стол» |
| 2 | Купить Диван угловой | 8=ПОИСК("диван";A2) | 11=ПОИСК("а";A2;5) | #ЗНАЧ!=ПОИСК("стол";A2) |
| 3 | диван-кровать | 1=ПОИСК("диван";A3) | 11=ПОИСК("а";A3;5) | #ЗНАЧ!=ПОИСК("стол";A3) |
Регистр не важен: «Диван» найден по запросу «диван». Третий аргумент задаёт, с какого символа начинать поиск. Если текста нет — #ЗНАЧ!.
В английском Excel: =SEARCH("диван",A2)
ПОИСК: формат результата и исходных данных
При поиске дат функция ПОИСК, как и все текстовые функции, воспринимает их как числа, поэтому для корректного поиска может понадобиться функция ТЕКСТ.
При этом логические значения ИСТИНА и ЛОЖЬ конвертируются в текст, соответствующий их написанию.
Поиск символа в ячейке
Наиболее простой пример использования функции - осуществление поиска определенного символа в ячейке.
Логика проста - если поиск позиции символа не возвращает ошибку, значит, символ в ячейке присутствует:
=НЕ(ЕОШ(ПОИСК(паттерн;текст)))
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Есть «@»? | То же через НЕ(ЕОШ) |
| 2 | ivan@mail.ru | ИСТИНА=ЕЧИСЛО(ПОИСК("@";A2)) | ИСТИНА=НЕ(ЕОШ(ПОИСК("@";A2))) |
| 3 | Иван Петров | ЛОЖЬ=ЕЧИСЛО(ПОИСК("@";A3)) | ЛОЖЬ=НЕ(ЕОШ(ПОИСК("@";A3))) |
ЕЧИСЛО и НЕ(ЕОШ(…)) дают одинаковый результат: число означает «найдено», ошибка — «нет».
В английском Excel: =ISNUMBER(SEARCH("@",A2))
Извлечь первое слово с помощью ПОИСК
В этом простейшем примере извлекаем первое слово из ячейки с помощью комбинации — функция ЛЕВСИМВ + функция ПОИСК. Поскольку пробел — регистронезависимый символ, для этого случая можно использовать и функцию НАЙТИ.
Таблица выше была использована для извлечения имени из строки с именем и фамилией.
- ПОИСК возвращает позицию пробела между именем и фамилией.
- Длина имени вычисляется как позиция пробела минус 1.
- Функция ЛЕВСИМВ извлекает имя на основе его длины.
| A | B | C | |
|---|---|---|---|
| 1 | ФИО | Позиция пробела | Имя |
| 2 | Анна Смирнова | 5=ПОИСК(" ";A2) | Анна=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1) |
| 3 | Олег Иванов | 5=ПОИСК(" ";A3) | Олег=ЛЕВСИМВ(A3;ПОИСК(" ";A3)-1) |
Если в ячейке одно слово, пробела нет и формула вернёт #ЗНАЧ!. Чтобы этого избежать, допишите пробел к тексту: ПОИСК(" ";A2&" ").
В английском Excel: =LEFT(A2,SEARCH(" ",A2)-1)
Другие примеры формул с ПОИСК
Найти первую цифру в ячейке:
=МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))
Найти первую цифру в ячейке и вернуть все, что перед ней:
=ЛЕВСИМВ(A1;МИН(ЕСЛИОШИБКА(ПОИСК({0:9:8:7:6:5:4:3:2:1};A1);1000))-1)
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Первая цифра | Текст до цифры |
| 2 | Заказ125 | 6=МИН(ЕСЛИОШИБКА(ПОИСК({0;9;8;7;6;5;4;3;2;1};A2);1000)) | Заказ=ЛЕВСИМВ(A2;МИН(ЕСЛИОШИБКА(ПОИСК({0;9;8;7;6;5;4;3;2;1};A2);1000))-1) |
| 3 | ул. Ленина 15 | 12=МИН(ЕСЛИОШИБКА(ПОИСК({0;9;8;7;6;5;4;3;2;1};A3);1000)) | ул. Ленина =ЛЕВСИМВ(A3;МИН(ЕСЛИОШИБКА(ПОИСК({0;9;8;7;6;5;4;3;2;1};A3);1000))-1) |
Ненайденные цифры дают ошибку, ЕСЛИОШИБКА заменяет её на 1000, и МИН выбирает настоящую позицию. Если цифр нет, получится 1000.
Узнать, содержит ли ячейка латиницу. Формула вернет «ИСТИНА» или «ЛОЖЬ»:
=СЧЁТ(ПОИСК({"a":"b":"c":"d":"e":"f":"g":"h":"i":"j":"k":"l":"m":"n":"o":"p":"q":"r":"s":"t":"u":"v":"w":"x":"y":"z"};A1))>0
Найти кириллицу в тексте аналогичным путем:
=СЧЁТ(ПОИСК({"а":"б":"в":"г":"д":"е":"ё":"ж":"з":"и":"й":"к":"л":"м":"н":"о":"п":"р":"с":"т":"у":"ф":"х":"ц":"ч":"ш":"щ":"ъ":"ы":"ь":"э":"ю":"я"};A1))>0
Функция ПОИСК в формуле массива
Примеры выше, где буквы перечислены явно в строковом массиве, занимает довольно много места. Буквы при этом идут подряд, что наводит на мысль, что их можно как-то иначе выразить как диапазон.
И действительно, это возможно с помощью комбинации с функциями СТРОКА и ПОИСК:
{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1))>0}
Отличие этой формулы массива от предыдущих — ее нужно вводить без фигурных скобок, они появятся при вводе формулы сочетанием Ctrl + Shift + Enter (вместо обычного Enter). В формуле выше, где явно прописаны все буквы, фигурные скобки вводятся вручную — это явное указание строкового массива.
Что происходит в этой формуле?
- Функция СТРОКА с численным аргументом "65:90" возвращает массив чисел с 65 по 90 включительно. Как раз в этом диапазоне в таблице ASCII находятся все символы латиницы;
- Функция СИМВОЛ возвращает для каждого числового значения в этом массиве его символ, таким образом создавая массив латинских символов;
- Функция ПОИСК производит поиск каждого из этих символов в строке и возвращает либо число, либо ошибку, таким образом создавая массив чисел и ошибок
- Функция СЧЁТ считает числовые значения в полученном массиве. Если результат больше нуля, значит, хотя бы один символ латиницы был найден. Если нет (все поиски вернули ошибку), значит, не был
Аналогичная формула для кириллицы:
{=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА(192:223));A1))>0}
Подробнее о поиске и извлечении кириллицы и латиницы в Excel можно почитать тут:
| A | B | C | |
|---|---|---|---|
| 1 | Текст | Есть латиница? | Есть кириллица? |
| 2 | iPhone 15 | ИСТИНА=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА($65:$90));A2))>0 | ЛОЖЬ=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА($192:$223));A2))>0 |
| 3 | Москва | ЛОЖЬ=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА($65:$90));A3))>0 | ИСТИНА=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА($192:$223));A3))>0 |
| 4 | Мoсква | ИСТИНА=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА($65:$90));A4))>0 | ИСТИНА=СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА($192:$223));A4))>0 |
В «Мoсква» буква «o» латинская — формула это показывает. Коды 192–223 зависят от кодировки Windows и не включают «Ё»; надёжнее ЮНИСИМВ(СТРОКА($1040:$1103)). В Excel 2019 и раньше — Ctrl+Shift+Enter.
Найти латиницу или кириллицу в тексте
Есть еще множество комбинаций функции ПОИСК с другими функциями Excel, смотрите разделы:
Функция ИЛИ
Функция И
Функция ЗНАЧЕН
Удалить первое слово в ячейке Excel
Смотрите также по теме:
ОБНАРУЖИТЬ (функционал надстройки !SEMTools)
Найти определенные символы в Excel
Найти числа в текстовых ячейках
Почему ПОИСК не находит текст — частые ошибки
- #ЗНАЧ!, хотя текст есть. В ячейке неразрывный пробел или похожая латинская буква. Проверьте символы функцией КОДСИМВ или ЮНИКОД.
- Нашлась часть другого слова. ПОИСК ищет фрагмент, а не слово: «кот» найдётся в «котлета». Для слова целиком ищите с пробелами: ПОИСК(" кот ";" "&A2&" ").
- Ищете звёздочку или знак вопроса — находится всё подряд. Это подстановочные знаки, поставьте перед ними тильду: "~*", "~?".
- Нужно учесть регистр. ПОИСК его не различает — используйте НАЙТИ.
Функция ПОИСК — частые вопросы
Как найти слово в ячейке Excel формулой?
Формулой =ЕЧИСЛО(ПОИСК("слово";A2)) — ИСТИНА, если слово есть. Без формул — Ctrl+F на листе.
Чем ПОИСК отличается от НАЙТИ?
ПОИСК не различает регистр и понимает подстановочные знаки ? и *, НАЙТИ учитывает регистр и подстановочных знаков не знает.
Что делать, если ПОИСК возвращает #ЗНАЧ!?
Так функция сообщает, что текст не найден. Оберните её в ЕЧИСЛО для проверки или в ЕСЛИОШИБКА, чтобы подставить своё значение.
Как найти данные на всём листе или в книге?
Ctrl+F → «Найти все», а в «Параметрах» выберите «Искать: в книге». Подробнее — поиск данных в Excel.
Как называется ПОИСК на английском?
SEARCH.
Найти ячейки с нужным текстом, словами из списка, латиницей, цифрами или по регулярному выражению сразу во всём диапазоне можно в пару кликов с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: функция ПОИСК в Excel
| Задача | Формула |
|---|---|
| Позиция текста | =ПОИСК("текст";A2) |
| Есть ли текст в ячейке | =ЕЧИСЛО(ПОИСК("текст";A2)) |
| Первое слово | =ЛЕВСИМВ(A2;ПОИСК(" ";A2&" ")-1) |
| С учётом регистра | =НАЙТИ("текст";A2) |
| Есть ли латиница | =СЧЁТ(ПОИСК(СИМВОЛ(СТРОКА($65:$90));A2))>0 |
Надеюсь, на этой странице вы нашли то, что искали. ПОИСК — основа почти всех формул, которые что-то проверяют или вырезают из текста.