Как в excel работает формула найти

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

Пример формулы НАЙТИ, ДЛСТР и ПРАВСИМВ в Excel

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

• PTR-422
• CORPO-6755
• SVCCALL-56532

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

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

Для реализации данной задачи следует использовать формулу с комбинацией функций ПРАВСИМВ, ДЛСТР и НАЙТИ:

Благодаря функции НАЙТИ можно автоматически определять позицию в текстовой строке для указанного символа в ее аргументах. А после использовать номер позиции в следующих операциях, например, при автоматической генерации значений для второго аргумента функции ПРАВСИМВ. Реализуется генерация путем определения необходимого числа при вычитании от длины строки, которую возвращает функция ДЛСТР номера позиции символа – «-».



Пример использования НАЙТИ и ПСТР в формуле Excel

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

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

Функция НАЙТИ требует заполнить минимум 2 из 3-х аргументов:

1. Искомый_текст – здесь необходимо указать текст, который следует найти и получить его порядковый номер (позицию) в исходной текстовой строке.
2. Просматриваемый_текст – тут указываем ссылку на ячейку с исходной строкой, которая содержит искомый символ или текст.
3. Нач_позиция – это не обязательный аргумент. Здесь можно указать номер позиции символа в строке, с которого следует начинать поиск. Если строка содержит более одного найденного искомого символа, то с помощью данного необязательного аргумента можно указать номер символа с которого будет просматриваться остальная часть строки. Если он не указан в этом аргументе, то по умолчанию он равен = 1, то есть с первого, а значит целая строка.

На пример, в примере функция находит первый дефис в строке «PWR-16-Small». В результате своего вычисления она по умолчанию возвращает число 4. Так как первый дефис в и сходной строке находится на четвертой позиции.

Динамические формулы с использованием функции НАЙТИ

Но если мы воспользуемся третьим необязательным аргументом и укажем в нем число 5. То есть просматривать первую не целиком, а начиная после первого дефиса, четвертого символа. Тогда функция будет возвращать нам порядковую позицию второго «-», то есть число – 7.

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

Если использовать формулу, указанную в примере мы вырезаем 2 числовых символа, которые находиться после первого дефиса из каждой строки кодов товаров. Обратите внимание на сложение в формуле +1, благодаря которому мы смещаем фокус функции на один символ, чтобы определить его позицию на символы после дефиса (минуя его).

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

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

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

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

Здесь мы использовали автоматический поиск первого дефиса. Номер позиции послужил третьим необязательным опциональным аргументом функции НАЙТИ для автоматического поиска каждого второго дефиса в каждом коде товара. Далее используя функцию ДЛСТР, мы определяем длину исходной строки и вычитаем от нее число позиции второго символа. Другими словами, от длины кода вычитаем количество символов до второго дефиса (включительно с ним, о чем свидетельствует сложение +1). Таким образом мы динамически определяем второй аргумент для функции ПРАВСИМВ чтобы вырезать разной величины фрагмент текста из строк. К том уже все строки с разной длиной, да еще и в разном месте находится второй дефис. Но умная формула справилась полностью в автоматическом режиме.

Функция

НАЙТИ(

)

, английский вариант FIND(),

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

Синтаксис функции

НАЙТИ

(

искомый_текст

;

просматриваемая_строка

;[нач_позиция])

Искомый_текст

— текст, который требуется найти.

Просматриваемая_строка

— текст, в которой ищется

Искомый_текст

.

Нач_позиция

— позиция знака в просматриваемой_строке, с которой должен начинаться поиск. Если аргумент

нач_позиция

опущен, то предполагается значение 1.

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

Функция

НАЙТИ()

учитывает РЕгиСТР

букв, т.е. результат поиска в строке ”

ПЕРВЫЙ первый

” будет разным для формул

=НАЙТИ(“ПЕРВЫЙ”;”ПЕРВЫЙ первый”)

и

=НАЙТИ(“первый”;”ПЕРВЫЙ первый”)

. Первая вернет 1, вторая 8.

Примеры

Формула

=НАЙТИ(“к”;”Первый канал”)

вернет 8, т.к. буква

к

находится на 8-й позиции слева. Формула

=НАЙТИ(“К”;”Первый канал”)

вернет значение ошибки #ЗНАЧ!, т.к. с учетом регистра строка

Первый канал

не содержит букву

К

.

Пусть в ячейке

А2

введена строка

Первый канал – лучший

. Формула

=НАЙТИ(СИМВОЛ(32);A2)

вернет 7, т.к. символ пробела (код 32) находится на 7-й позиции.

Чтобы найти позицию второго вхождения буквы “а” в строке “мама мыла раму” используйте формулу

=НАЙТИ(“а”;”мама мыла раму”;НАЙТИ(“а”;”мама мыла раму”)+1).

Чтобы определить есть ли третье вхождение буквы “м” в строке “мама мыла раму” используйте формулу

=ЕСЛИ(ДЛСТР(ПОДСТАВИТЬ(“мама мыла раму”;”м”;””;3))=ДЛСТР(“мама мыла раму”);”Нет третьего вхождения”;”Есть третье вхождение”)

.

Функция

НАЙТИ()

vs

ПОИСК()

Функция

НАЙТИ()

учитывает РЕгиСТР

букв и не допускает использование

подстановочных знаков

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

подстановочных знаков

пользуйтесь функцией

ПОИСК()

.

Связь с функциями

ЛЕВСИМВ()

,

ПРАВСИМВ()

и

ПСТР()

Функция

НАЙТИ()

может быть использована совместно с функциями

ЛЕВСИМВ()

,

ПРАВСИМВ()

и

ПСТР()

.

Например, в ячейке

А2

содержится фамилия и имя “Иванов Иван”, то формула

=ЛЕВСИМВ(A2;НАЙТИ(СИМВОЛ(32);A2)-1)

извлечет фамилию, а

=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(СИМВОЛ(32);A2))

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

СЖПРОБЕЛЫ()

.

Функция НАЙТИ (FIND) в Excel используется для поиска текстового значения внутри строчки с текстом и указать порядковый номер буквы с которого начинается искомое слово в найденной строке.

Что возвращает функция

Возвращает числовое значение, обозначающее стартовую позицию текстовой строчки внутри другой текстовой строчки.

Синтаксис

=FIND(find_text, within_text, [start_num]) — английская версия

=НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция]) — русская версия

Аргументы функции

• find_text (искомый_текст) — текст или строка которую вы хотите найти в рамках другой строки;
• within_text (просматриваемый_текст) — текст, внутри которого вы хотите найти аргумент find_text (искомый_текст);
• [start_num] ([нач_позиция]) — число, отображающее позицию, с которой вы хотите начать поиск. Если аргумент не указать, то поиск начнется сначала.

Дополнительная информация

• Если стартовое число не указано, то функция начинает поиск искомого текста с начала строки;
• Функция НАЙТИ чувствительна к регистру. Если вы хотите сделать поиск без учета регистра, используйте функцию SEARCH в Excel;
• Функция не учитывает подстановочные знаки при поиске. Если вы хотите использовать подстановочные знаки для поиска, используйте функцию SEARCH в Excel;
• Функция каждый раз возвращает ошибку, когда не находит искомый текст в заданной строке.

Примеры использования функции НАЙТИ в Excel

Пример 1. Ищем слово в текстовой строке (с начала строки)

На примере выше мы ищем слово «Доброе» в словосочетании «Доброе Утро». По результатам поиска, функция выдает число «1», которое обозначает, что слово «Доброе» начинается с первой по очереди буквы в, заданной в качестве области поиска, текстовой строке.

Обратите внимание, что так как функция НАЙТИ в Excel чувствительна к регистру, вы не сможете найти слово «доброе» в словосочетании «Доброе утро», так как оно написано с маленькой буквы. Для того, чтобы осуществить поиска без учета регистра следует пользоваться функцией SEARCH.

Пример 2. Ищем слово в текстовой строке (с заданным порядковым номером старта поиска)

Третий аргумент функции НАЙТИ указывает позицию, с которой функция начинает поиск искомого значения. На примере выше функция возвращает число «1» когда мы начинаем поиск слова «Доброе» в словосочетании «Доброе утро» с начала текстовой строки. Но если мы зададим аргумент функции start_num (нач_позиция) со значением «2», то функция выдаст ошибку, так как начиная поиск со второй буквы текстовой строки, она не может ничего найти.

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

Пример 3. Поиск текстового значения внутри текстовой строки с дублированным искомым значением

На примере выше мы ищем слово «Доброе» в словосочетании «Доброе Доброе утро». Когда мы начинаем поиск слова «Доброе» с начала текстовой строки, то функция выдает число «1», так как первое слово «Доброе» начинается с первой буквы в словосочетании «Доброе Доброе утро».

Но, если мы укажем в качестве аргумента start_num (нач_позиция) число «2» и попросим функцию начать поиск со второй буквы в заданной текстовой строке, то функция выдаст число «6», так как Excel находит искомое слово «Доброе» начиная со второй буквы словосочетания «Доброе Доброе утро» только на 6 позиции.