Найти слово в строке в excel. Поиск в программе Microsoft Excel

Microsoft Excel

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

Извлечение первого слова из строки

Чтобы извлечь 1-ое слово из строчки, формула обязана отыскать позицию первого знака пробела, а потом употреблять эту информацию в качестве аргумента для функции ЛЕВСИМВ. Последующая формула делает это: .

Эта формула возвращает весь текст до первого пробела в ячейке A1. Но у нее есть маленькой недостаток: она возвращает ошибку, ежели текст в ячейке А1 не содержит пробелов, поэтому что состоит из 1-го слова. Несколько наиболее непростая формула решает делему с помощью новейшей функции ЕСЛИОШИБКА, отображая все содержимое ячейки, ежели произошла ошибка:
.

Если для вас необходимо, чтоб формула была совместима с наиболее ранешними версиями Excel, вы не сможете применять ЕСЛИОШИБКА. В таком случае придется обойтись функцией ЕСЛИ и функцией ЕОШ для проверки на ошибку:

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

Извлечение крайнего слова строчки — наиболее непростая задачка, так как функция НАЙТИ работает лишь слева направо. Таковым образом, неувязка состоит в поиске крайнего знака пробела. Последующая формула, но, решает эту делему. Она возвращает крайнее слово строчки (весь текст, последующий за крайним эмблемой пробела):

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

Следующая формула совместима со всеми версиями Excel:

Извлечение всего, не считая первого слова строки

Следующая формула возвращает содержимое ячейки А1, за исключением первого слова:
.
Ежели ячейка А1 содержит текст 2008 Operating Budget, то формула вернет Operating Budget.

Формула возвращает ошибку, ежели ячейка содержит лишь одно слово. Последующая версия формулы употребляет функцию ЕСЛИОШИБКА, чтоб можно было избежать ошибки; формула возвращает пустую строчку, ежели ячейка не содержит наиболее 1-го слова:

А эта версия совместима со всеми версиями Excel:

Пример достоинства функции ПОИСК в Excel перед функцией НАЙТИ

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

Читайте также  Запрашивает права администратора. Нет прав администратора у учётной записи Windows: что делать?

Примеры использования функции ПОИСК в Excel

Для нахождения позиции текстовой строчки в иной аналогичной используют ПОИСК и ПОИСКБ. Расчет ведется с первого знака анализируемой ячейки. Так, ежели задать функцию ПОИСК “л” для слова «апельсин» мы получим значение 4, так как конкретно таковой по счету выступает данная буковка в текстовом выражении.

Функция ПОИСК работает не лишь для поиска позиции отдельных букв в тексте, но и для целой композиции. К примеру, задав данную команду для слов «book», «notebook», мы получим значение 5, так как конкретно с этого по счету знака начинается разыскиваемое слово «book».

Используют функцию ПОИСК наряду с таковыми, как:

  • НАЙТИ (осуществляет поиск с учетом регистра);
  • ПСТР (возвращает текст);
  • ЗАМЕНИТЬ (заменяет символы).

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

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

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

=ПОИСК(нужный_текст;анализируемый_текст;[начальная_позиция]).

В данной формуле задаваемые значения определяются последующим образом.

  1. Искомый текст. Это числовая и буквенная композиция, позицию которой требуется отыскать.
  2. Анализируемый текст. Это тот фрагмент текстовой инфы, из которого требуется вычленить разыскиваемую буковку либо сочетание и вернуть позицию.
  3. Начальная позиция. Данный фрагмент необязателен для ввода. Но, ежели вы желаете отыскать, к примеру, буковку «а» в строке со значением «А015487.Мужская одежда», то нужно указать в конце формулы 8, чтоб анализ этого фрагмента проводился с восьмой позиции, то есть опосля артикула. Ежели этот аргумент не указан, то он по умолчанию считается равным 1. При указании исходной позиции положение искомого фрагмента все равно будет считаться с первого знака, даже ежели исходные 8 были пропущены в анализе. То есть в рассматриваемом примере буковке «а» в строке «А015487.Мужская одежда» будет присвоено значение 14.

При работе с аргументом «искомый_текст» можно применять последующие подстановочные знаки.

  1. Вопросительный символ (?). Он будет соответствовать хоть какому знаку.
  2. Звездочка (*). Этот знак будет соответствовать хоть какой композиции символов.

Если же требуется отыскать подобные знаки в строке, то в аргументе «искомый_текст» перед ними необходимо поставить тильду (~).

Если разыскиваемый текст не был найден приложением либо исходная позиция установлена меньше 0, больше общего количества присутствующих знаков, в ячейке отобразиться ошибка #ЗНАЧ.

Читайте также  Как открыть папки невидимые. Как сделать и открыть скрытые, невидимые файлы и папки

Если «искомый_текст» не найден, ворачивается значение ошибки #ЗНАЧ.



Пример использования функции ПОИСК и ПСТР

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

Введем начальные данные в таблицу:

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

=ПОИСК(“, тел.”;адрес_анализируемой_ячейки).

Нажмем Enter для отображения разыскиваемой информации:

Далее мы можем применять любые остальные функции для отображения представленной инфы в комфортном формате:

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

Пример формулы ПОИСК и ЗАМЕНИТЬ

Пример 2. Есть таблица с текстовой информацией, в которой слово «маржа» необходимо заменить на «объем».

Откроем книжку Excel с обрабатываемыми данными. Пропишем формулу для поиска подходящего слова «маржа»:

Теперь дополним формулу функцией ЗАМЕНИТЬ:

Чем различается функция ПОИСК от функции Отыскать в Excel?

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

Но опытнейший юзер Excel знает, что отличие у этих 2-ух функций чрезвычайно существенные.

Отличие №1. Чувствительность к верхнему и нижнему регистру (большие и мелкие буквы). Функция Отыскать чувствительна к регистру знаков. К примеру, есть перечень номенклатурных единиц с артикулом. Нужно отыскать позицию малеханькой буковкы «о».

Теперь смотрите как ведут себя по-разному эти две функции при поиске большой буковкы «О» в аспектах поиска:

Отличие №2. В первом аргументе «Искомый_текст» для функции ПОИСК мы можем применять знаки подстановки для указания не четкого, а приблизительного значения, которое обязано содержаться в начальной текстовой строке. 2-ая функция Отыскать не умеет употреблять в работе знаки подстановки масок текста: «*»; «?»; «~».

Для примера попробуем в этих же начальных строчках столбца «наименования» отыскать приблизительный текст. Для этого укажем последующий вид аспекта поиска используя знаки подстановки: «н*ая».

Скачать примеры функций ПОИСК и Отыскать в Excel

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

Поиск в програмке Microsoft Excel

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

По умолчанию, функции «Учитывать регистр» и «Ячейки целиком» отключены, но, ежели мы поставим галочки около соответственных пт, то в таком случае, при формировании результата будет учитываться введенный регистр, и четкое совпадение. Ежели вы введете слово с малеханькой буковкы, то в поисковую выдачу, ячейки содержащие написание этого слова с большой буковкы, как это было бы по умолчанию, уже не попадут. Не считая того, ежели включена функция «Ячейки целиком», то в выдачу будут добавляться лишь элементы, содержащие четкое наименование. К примеру, ежели вы зададите поисковый запрос «Николаев», то ячейки, содержащие текст «Николаев А. Д.», в выдачу уже добавлены не будут.

Читайте также  Служба svchost грузит процессор. Svchost.exe грузит процессор, что делать?

По умолчанию, поиск делается лишь на активном листе Excel. Но, ежели параметр «Искать» вы переведете в позицию «В книге», то поиск будет производиться по всем листам открытого файла.

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

В графе «Область поиска» определяется, посреди каких непосредственно частей делается поиск. По умолчанию, это формулы, то есть те данные, которые при клике по ячейке показываются в строке формул. Это может быть слово, число либо ссылка на ячейку. При этом, программа, выполняя поиск, лицезреет лишь ссылку, а не итог. о этом эффекте велась речь выше. Для того, чтоб создавать поиск конкретно по результатам, по тем данным, которые показываются в ячейке, а не в строке формул, необходимо переставить переключатель из позиции «Формулы» в позицию «Значения». Не считая того, существует возможность поиска по примечаниям. В этом случае, переключатель переставляем в позицию «Примечания».

Ещё наиболее точно поиск можно задать, нажав на клавишу «Формат».

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

Если вы желаете применять формат некий определенной ячейки, то в нижней части окна нажмите на клавишу «Использовать формат данной для нас ячейки…».

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

После того, как формат поиска настроен, нажимаем на клавишу «OK».

Бывают случаи, когда необходимо произвести поиск не по определенному словосочетанию, а отыскать ячейки, в которых находятся поисковые слова в любом порядке, даже, ежели их разделяют остальные слова и знаки. Тогда данные слова необходимо выделить с обеих сторон знаком «*». Сейчас в поисковой выдаче будут отображены все ячейки, в которых находятся данные слова в любом порядке.

Оставьте комментарий