Функция поиск эксель: ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

12 текстовых функций Excel на каждый день

Василий

16:26:00

текст

,

формула

,

функция

,

Эксель

,

Excel

,

Excel для новичков

,

Excel для чайников

При использовании Excel часто необходимо работать не только с числами, но и с текстом. В этой статье мы разберем 12 основных функций Excel для обработки текста.

Для примера возьмем строку «ExcelGuide.ru – про Excel и не только» и ее будем использовать в наших функциях ниже.

ЛЕВСИМВ

Функция ЛЕВСИМВ возвращает указанное количество знаков с начала строки. В качестве аргументов на первом месте указываем ту строку, из которой хотим извлечь текст, а вторым аргументом количество символов, которое хотим получить.
Давайте из нашей строки получим текст «ExcelGuide.ru»:

=ЛЕВСИМВ(B1;13)

ПРАВСИМВ

Функция ПРАВСИМВ аналогична ЛЕВСИМВ, только возвращает указанное количество символов не с начала, а с конца строки. Первым аргументом указываем строку, откуда будем получать часть текста, а вторым аргументом – количество символов.
Из нашей строки извлечем текст «про Excel и не только»:

=ПРАВСИМВ(B1;21)

ПСТР

Функция ПСТР позволяет получить указанное количество символов начиная с определенной позиции. У этой функции 3 аргумента: Текст, из которого нам нужно получить часть; стартовая позиция, с которой нужно извлечь символы; количество символов, которое хотим получить.
В нашей строке есть слово Excel, давайте его получим:

=ПСТР(B1;21;5)

ДЛСТР

Функция ДЛСТР возвращает количество символов в строке.

=ДЛСТР(B1)

ПОИСК

Функция ПОИСК предназначена для нахождения первого вхождения указанного текста в исходную строку. Аргументы функции: сначала указываем тот текст, который хотим найти; далее строку, в которой ищем текст.
Давайте в нашем примере найдем текст «про Excel»:

=ПОИСК(«про Excel»;B1)

СЦЕПИТЬ

Функция СЦЕПИТЬ позволяет последовательно объединить несколько текстовых элементов в одну строку.
В качестве аргументов необходимо перечислить те текстовые элементы, которые вы хотите соединить.
 В качестве примера объединим наш пример и строку «. Пожалуй лучший сайт про Excel )))»:

=СЦЕПИТЬ(B1;». Пожалуй лучший сайт про Excel )))»)

СОВПАД

Функция СОВПАД проверяет идентичность двух строк и возвращает Истина, если строки совпадают и ЛОЖЬ, если строки не совпадают.
Сравним нашу строку с текстом «ExcelGuide.ru»:

=СОВПАД(B1;»ExcelGuide. ru»)

СЖПРОБЕЛЫ

Функция СЖПРОБЕЛЫ удаляет лишние дублирующие пробелы. В качестве аргумента указываем строку, у которой надо удалить лишние пробелы.

=СЖПРОБЕЛЫ(B1)

ЗНАЧЕН

Функция ЗНАЧЕН преобразует текст в число. Часто случается при экспорте из разных информационных систем мы получаем числовые значения в текстовом формате, в таких случаях нам и пригодится этот функционал.
В качестве примера преобразуем текст «1000» в число 1 000:

=ЗНАЧЕН(«1000»)

ПОДСТАВИТЬ

Функция ПОДСТАВИТЬ заменяет новым текстом старый текст в исходной текстовой строке. Аргументов у функции три: сначала указываем ту строку, в которой будем менять текст; далее указываем старый текст; а затем тот, которым мы хотим заменить.
В качестве примера в нашей строке заменим «про» на «о»:

=ПОДСТАВИТЬ(B1;»про»;»о»)

ПРОПИСН

Функция ПРОПИСН преобразует все буквы в прописные. У функции только один аргумент – та строка, которую надо преобразовать.

=ПРОПИСН(B1)

СТРОЧН

Функция СТРОЧН преобразует все буквы в строчные. У функции один аргумент – тот текст, который мы хотим модифицировать.

=СТРОЧН(B1)

Кстати, если вы хотите более подробно изучить Excel, научиться строить быстро сложные отчеты и графики, то рекомендую вам курс «Excel + Google Таблицы с нуля до PRO» от Skillbox.

Спасибо за внимание. Мы разобрали 12 основных текстовых функций в Excel, которые вам могут пригодиться в ежедневной работе.

ᐉ Тендеры и госзакупки в Казахстане • Поиск тендеров • Поиск государственных закупок

Возможности

Ежедневная рассылка на Email и в Telegram

Подписывайтесь на ежедневную Email рассылку или получайте моментальные уведомления в Telegram, чтобы максимально сэкономить время на поиске закупок.

Поиск внутри документации

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

Попробовать

Поиск по планам, закупкам и договорам

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

Планы Закупки Договоры

Отслеживание статусов

Уведомляем об изменении статусов лотов от предварительного обсуждения до подведения итогов. Отслеживание статусов поможет подавать необходимые документы в срок без риска пропуска важных дедлайнов.

Проверка контрагентов по 90+ показателям

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

Попробовать

Мониторинг конкурентов

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

Попробовать

Поиск квалифицированных сотрудников

Наш реестр «Сотрудники с сертификатами» поможет вам найти требуемых квалифицированных специалистов для участия в закупках.

Попробовать

Доступ к поставщикам и производителям

В реестре вы сможете найти ТОП 50 поставщиков по каждому товару. Выгружайте информацию в удобном для вас формате и используйте для своего бизнеса.

Попробовать

API для любой информационной системы

Интеграция API позволяет автоматизировать процессы и получать информацию о закупках в удобном формате в вашей CRM системе.

15+ лет

на рынке
Казахстана

146 528

тендеров
проводится сегодня

200 000+

пользователей
сервиса

Почему zakupki.kz?

Более 100+ тендерных площадок

Предоставляем нашим клиентам полную базу всех закупок Казахстана.

Изучайте документацию без регистрации на первоисточниках

Нет необходимости регистрироваться на первоисточниках, смотрите все в одном сервисе.

Узнавайте о закупках первым

Отправляем ежедневную рассылку на почту с новыми тендерами два раза в день и моментальные уведомления в Telegram сразу после публикации закупки.

Персональный менеджер и индивидуальные консультации

Сопровождение персонального менеджера на всех этапах работы с zakupki.kz.

Удобные выгрузки в Excel

Скачивайте информацию о закупках в формате Excel таблиц для дальнейшей работы.

Фильтры для любого запроса

Удобные параметры поиска мгновенно найдут то, что требуется именно вам.

Начните выигрывать с нами!

Узнайте подробнее о наших тарифах и выберите
подходящий для себя

Смотреть тарифы

Наши клиенты и партнеры

Отзывы

SEARCH Fx — поиск подстроки в строке — таблицы Excel, VBA и G

В этом руководстве показано, как использовать функцию SEARCH в Excel и Google Sheets для определения положения текста в ячейке.

Что такое ПОИСК?

Функция ПОИСК Excel «ищет» строку текста в другой строке. Если текст найден, ПОИСК возвращает числовую позицию строки.

Примечание. ПРИ ПОИСКЕ НЕ учитывается регистр. Это означает, что «текст» будет соответствовать «ТЕКСТ». Для поиска текста с учетом регистра используйте функцию НАЙТИ.

Как использовать функцию ПОИСК

Функция ПОИСК в Excel работает следующим образом.

 =ПОИСК("е", B3)
 

Здесь Excel вернет 1, так как «е» — это первый символ в слове «слон».

Ниже приведены еще несколько примеров.

Начальный номер (start_num)

При желании вы можете определить начальный номер (start_num). start_num сообщает функции SEARCH, где начать поиск. Если оставить поле пустым, поиск начнется с первого символа.

 =ПОИСК(B3,C3) 

Теперь давайте установим start_num равным 2, что означает, что ПОИСК будет искать со второго символа.

 =ПОИСК(B3,C3,D3) 

В этом случае ПОИСК возвращает 3: положение второй буквы «е».

Важно: start_num не влияет на возвращаемое значение, ПОИСК всегда будет начинать отсчет с первого символа.

Начальный номер (start_num) Ошибки

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

 =ПОИСК(B3,C3,D3) 

Неудачные поиски Возвращают #ЗНАЧ! Ошибка

Если ПОИСК не может найти искомое значение, Excel вернет #ЗНАЧ! ошибка.

Поиск без учета регистра

В приведенном ниже примере показано, что функция ПОИСК не учитывает регистр. Мы искали «abc», но ПОИСК вернул 1, потому что он соответствовал «ABC».

Поиск по шаблону

Вы можете использовать подстановочные знаки с ПОИСКОМ, которые позволяют искать неуказанные символы.

Знак вопроса в тексте поиска означает «любой символ». Таким образом, «?000» в приведенном ниже примере означает «Поиск любого символа, за которым следуют три нуля».

Звездочка означает «любое количество неизвестных символов». Здесь мы ищем «1 * C», и ПОИСК возвращает 2, потому что он соответствует этому с «1-ABC».

В следующем примере мы ищем «000?» – то есть «000», за которым следует любой символ. У нас есть «000», но он находится в конце строки и поэтому не сопровождается никакими символами, поэтому мы получаем ошибку 9.0007

Однако, если мы использовали звездочку вместо вопросительного знака – то есть «000*» вместо «000?», мы получаем совпадение. Это связано с тем, что звездочка означает «любое количество символов», включая отсутствие символов.

Как разделить имя и фамилию из ячейки с помощью ПОИСКА

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

Получение имени

Функция ВЛЕВО возвращает определенное количество символов из строки, начиная слева.

Если мы используем ПОИСК, чтобы найти положение пробела между именем и фамилией, вычтем из этого 1, мы знаем, насколько длинное имя. Тогда мы можем просто передать это в LEFT.

Формула имени:

 =ЛЕВЫЙ(B3,ПОИСК(" ",B3)-1) 

Получение фамилии

Функция RIGHT возвращает определенное количество символов справа от строки.

Чтобы получить количество символов, равное длине фамилии, мы используем ПОИСК, чтобы сообщить нам положение пробела, а затем вычитаем это число из общей длины строки, что мы можем получить с помощью ДЛСТР.

Формула фамилии:

 =ПРАВО(B3,ДЛСТР(B3)-ПОИСК(" ",B3)) 

Обратите внимание, что если данные вашего имени содержат отчества, отчество будет разделено на Ячейка «Фамилия».

Использование ПОИСКА для возврата n-го символа в строке

Как отмечалось выше, ПОИСК возвращает позицию первого найденного совпадения. Но, комбинируя его с CHAR и SUBSTITUTE, мы можем использовать его для поиска более поздних вхождений символа, таких как второе или третье вхождение.

Вот формула:

 =ПОИСК(СИМВОЛ(134),ПОДСТАВИТЬ(D3,C3,СИМВОЛ(134),B3)) 

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

• Мы используем ПОИСК и ищем строку «CHAR(134)». CHAR возвращает символ на основе его кода ASCII. CHAR(134) — это символ кинжала — вы можете использовать здесь что угодно, если оно не появляется в вашей реальной строке.
• SUBSTITUTE перебирает строку и заменяет один символ или подстроку другим. Здесь мы заменяем строку, которую хотим найти (которая находится в C3), на CHAR(134). Причина, по которой это работает, заключается в том, что четвертый параметр SUBSTITUTE — это номер экземпляра, который мы сохранили в B3.
• Итак, ПОДСТАВИТЬ меняет местами n-й символ в строке на символ крестика, а затем ПОИСК возвращает его позицию.

Вот как это выглядит:

Найти среднюю часть строки

Представьте себе много серийных номеров со следующим форматом:

чтобы вытащить среднюю часть каждого из них. Вместо того, чтобы делать это вручную, вы можете комбинировать SEARCH с MID, чтобы автоматизировать эту задачу.

Функция MID возвращает часть строки. Его входными данными являются текстовая строка, начальная точка и ряд символов.

Поскольку начальной точкой, которую мы хотим, является символ после дефиса, мы можем использовать ПОИСК, чтобы получить положение дефиса, и добавить к нему 1. Если бы серийный номер был в B3, мы бы использовали:

 =ПОИСК("-",B3)+1 

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

 =ПОИСК("/",B3)-ПОИСК("-",B3)-1 

Затем мы просто подставляем эти две формулы в MID:

ПОИСК против ПОИСКА

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

• ПОИСК чувствителен к регистру, а ПОИСК не

.

• НАЙТИ не разрешает подстановочные знаки, а ПОИСК разрешает

Вы можете увидеть несколько примеров этих различий ниже:

ПОИСК в Google Таблицах

Функция ПОИСК работает точно так же в Google Таблицах, как и в Excel:

Функция ПОИСК3 9001 Дополнительные примечания 9001 является нечувствительной к регистру версией функции НАЙТИ. ПОИСК также поддерживает подстановочные знаки. Найти не дает.

ПОИСК Примеры в VBA

Вы также можете использовать функцию ПОИСК в VBA. Тип:

 application.worksheetfunction.search(find_text,within_text,start_num) 

Для аргументов функции (find_text и т. д.) вы можете либо ввести их непосредственно в функцию, либо определить переменные для использования вместо них.

Возврат к списку всех функций в Excel

11 примеров для изучения функций поиска и поиска в Excel (незначительные отличия)

Функция НАЙТИ чувствительна к регистру для возврата позиции. «C» и «c» имеют разные значения, если вы используете метод FIND для получения позиции текста в исходной строке. Функция ПОИСК нечувствительна к регистру. Вы не можете использовать подстановочные знаки «*» и «?» в функции НАЙТИ.

Предположим, у нас есть предложение «У Excel много функций». Мы хотим найти позицию «множество» в этом предложении. Позиция означает, с какого числа в этом предложении начинается множество.

Для этого созданы функции ПОИСК и НАЙТИ в Excel. Обе функции принимают три аргумента; два уже должно быть ясно. См. почти одинаковый синтаксис НАЙТИ и ПОИСК:

НАЙТИ(Текст_для_поиска, Исходный_текст, [начальный_номер])

SEARCH(find_text,within_text,[start_num])

• Текст для поиска – «Много» в нашем примере предложения.
• Source_Text — Предложение, которое мы использовали. Естественно, это может быть ссылка на ячейку.
• Третий необязательный параметр — начальный номер . Он указывает, с какой позиции вы хотите начать поиск в исходном тексте. Например, мы хотим найти «с» в приведенном выше предложении из числа 6. Я покажу это на примере, чтобы было понятно.
• Если find_text не найден, #VALUE! Произошла ошибка, что вы можете управляться с помощью IFERROR.

• 1.
  Разница между ПОИСК и НАЙТИ в Excel

• 2.
  Пример функции НАЙТИ

• 3.
  Функция Excel НАЙТИ является примером с учетом регистра

• 4.
  Использование функции НАЙТИ в тексте ячейки

• 5.
  Пример аргумента Start_Num в функции НАЙТИ

• 6.
  Обработка ошибки, если текст не найден функцией ЕСЛИОШИБКА

• 7.
  Функция ПОИСК Excel

• 8.
  Использование подстановочных знаков в функции ПОИСК

Разница между ПОИСК и НАЙТИ в Excel

Есть два отличия:

• Функция НАЙТИ чувствительна к регистру. «C» и «c» имеют разные значения, если вы используете функцию FIND для получения позиции текста в исходной строке.
• С другой стороны, функция ПОИСК нечувствительна к регистру.
• Вы не можете использовать подстановочные знаки «*» и «?» в функции НАЙТИ.
• Функция ПОИСК позволяет использовать подстановочные знаки.

См. следующий раздел с примерами для изучения функций и понимания различий.

Пример функции НАЙТИ

Позвольте мне начать с простого примера использования предложения и поиска позиции слова в этом предложении. Формула НАЙТИ применяется в ячейке D11 следующим образом:

=НАЙТИ(«Имя»,»Меня зовут Бен»)

Результат:

Слово «Имя» начинается с позиции 4 в данном предложении.

Знаете ли вы, что в Excel есть функции FINDB и SEARCHB, которые работают с языками, использующими набор двухбайтовых символов (DBCS). ПОИСК/ПОИСК предназначен для набора однобайтовых символов (SBCS) – каждый символ считается равным 1.

Пример функции ПОИСК в Excel с учетом регистра

Теперь давайте рассмотрим пример функции ПОИСК с учетом регистра. Используя то же предложение, что и выше, за исключением того, что мы найдем местоположение «имя»:

.

=НАЙТИ(«имя»,»Меня зовут Бен»)

Видите ли, #ЗНАЧ! Произошла ошибка , когда мы изменили регистр «N» на «n» в функции НАЙТИ.

Использование функции НАЙТИ в текстовой ячейке

В этом примере я буду использовать функцию НАЙТИ для поиска позиции буквы в ячейке A7. Столбец A содержит имена сотрудников только для демонстрации:

Формула НАЙТИ:

= НАЙТИ («ч», A7)

Результирующий лист:

Вы можете видеть, что положение «h» равно 2 в тексте A1 = Shabee

Пример аргумента Start_Num в функции FIND

2 Пример параметра start_num, текст в ячейке B3 ищется.

B3 содержит следующий текст:

«ИТ-эксперт / Excel / Powerpoint / Word»

Мы искали ‘p’, который встречается в позициях 6 и 26. Однако аргумент start_num указан, как показано ниже, и посмотрите результат:

= FIND(B10, B3,8)

Результат:

Понимаете, он вернул 26, потому что НАЙТИ начал поиск с позиции 8.

Обработка ошибки, если текст не найден функцией ЕСЛИОШИБКА

сообщение об ошибке, если данный поисковый термин не найден, вы можете использовать функцию ЕСЛИОШИБКА с НАЙТИ для отображения описательного сообщения, например. «Поисковое слово не найдено».

См. пример ниже, где пользователь может ввести любой текст в ячейку B10. Введенный текст будет искаться в ячейке B3. Я использовал комбинацию ЕСЛИОШИБКА/НАЙТИ и ввел различные тексты, в том числе несуществующий. Посмотрите на формулу и результаты:

Формула:

=ЕСЛИОШИБКА(НАЙТИ(B10,B3), «Заданный текст не найден!»)

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

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

Функция ПОИСК Excel

Все приведенные выше примеры будут работать одинаково, если вы просто замените слово «НАЙТИ» на «ПОИСК», за исключением примера с учетом регистра. Итак, я не буду снова показывать все эти примеры для функции ПОИСК, а продолжу, показав, чем она отличается от функции НАЙТИ.

Итак, давайте сначала посмотрим на нечувствительность к регистру функции ПОИСК.

Для этого я использую тот же лист, что и в приведенном выше примере. В формуле ПОИСК я указал ячейку B8, содержащую текст «Счета». Посмотрите на результаты поиска различных текстов по этой формуле:

Формула ПОИСК:

=ПОИСК(B10,B8)

Результаты:

27 ” и возвращается позиция 1,

«T» не используется в «Учетных записях», но ПОИСК вернул позицию «t».