Excel подстрока формула: ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

Содержание

ПСТР в Excel (функция получения подстроки) | MS Office

Функция ПСТР в Excel предназначена для получения части строки (подстроки) и может применяться для того, чтобы вырезать часть строки. Нужная часть строки определяется номерами начальной и конечной позиций символов в указанной строке.

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

Поскольку целью является получение подстроки, то очевидно, что задать нужно следующие параметры:
Текст
Текстовая строка, часть которой требуется получить. Это может быть ссылка на ячейку (чаще всего), текстовая константа (имеет смысл только для обучения) или результат работы другой функции.
Начальная позиция
Считается слева и указывает функции ПСТР, откуда начинается нужный нам фрагмент строки (подстрока). Нумерация начинается с 1, а не с нуля!
Число знаков
Сколько символом нужно получить. Минимум 1. Если указать 0, то на выходе получим пустую строку (ничего).
Сама формула в обобщённом виде выглядит следующим образом:
ПСТР(текст; начальная_позиция; число_знаков)
Вообще в руководстве Excel есть ещё функция ПСТРБ, предназначенная для работы с мультибайтовыми строками, но в нашей версии программы эта функция не поддерживается.
Если у Вас она окажется, то имейте в виду, что вместо числа знаков последним параметром указывается число байт, поскольку один символ занимает больше одного байта. Этот случай мы не рассматриваем.
На выходе вы получим подстроку или ошибку вида «#ЗНАЧ!», если какие-то параметры были заданы неверно. Например, позиция начала подстроки может быть указана с нуля или число символов в подстроке отрицательное. Про обработку ошибок мы писали ранее.
Можно запомнить следующие особенности функции ПСТР:
Если значение «начальная_позиция» больше, чем длина текста, то функция ПСТР возвращает строку «» (пустую строку).
Если значение «начальная_позиция» меньше, чем длина текста, но сумма значений «начальная_позиция» и «число_знаков» превышают длину текста, функция ПСТР возвращает знаки вплоть до конца текста.
Если значение «начальная_позиция» меньше 1, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
Если значение «число_знаков» отрицательно, то функция ПСТР возвращает значение ошибки #ЗНАЧ!.
Если значение «число_байтов» отрицательно, то функция ПСТРБ возвращает значение ошибки #ЗНАЧ!.
К языкам, поддерживающим двухбайтовую кодировку, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский. Если Вы работаете с русским или английским языками, то никаких проблем функция ПСТР не доставит и всё достаточно просто. Дополнительную информацию по ПСТР в Excel можно прочитать в справке самой программы, приведённой после статьи, а также внутри самого Excel.
Как в Excel получить часть строки (подстроку) при помощи функции ПСТР
Лучше всего посмотрите видео и скачайте прикреплённый файл с примерами
, чтобы была возможность потренироваться с получанием подстроки в Excel. Теория без практики мало чем поможет, потому что функций много и всего не запомнить.
В качестве альтернативного примера, которого нет на видео, посмотрим способ получения отдельных символов из заранее подготовленной строки. Ниже представлена строка, в которой содержатся цифры (самый примитивный пример; кавычки не считаются).
Строка в ячейке B14: «1234567890»
Допустим, нам нужно получить цифру 3. В таком случае выражение будет выглядеть так: «=ПСТР(B14;3;1)». То есть мы указываем, что нужно взять один символ начиная с третьей позиции в тексте. В результате мы как раз и получим цифру 3. На практике этот подход можно использовать для получения различных знаков из предустановленного набора символов.

Остальные примеры, вполне типовые, смотрите в прикреплённом файле и на видео.
Если Вы хотите нормально пользоваться Excel и понимать, как работает программа, рекомендуем приобрести специальный видеокурс, с описанием и примерами уроков которого можно познакомиться здесь.
Если же Вы и так хорошо знаете программы, придумайте интересный пример получения подстроки при помощи функции ПСТР и опубликуйте его в комментариях в помощь менее опытным нашим читателям!
Уникальная метка публикации: EB2DC2E6-8F14-45C2-7D71-12C9E770C1D6
Источник: //artemvm. info/information/uchebnye-stati/microsoft-office/pstr-v-excel-funkcziya/
Смотреть видео
ПСТР в Excel (функция получения подстроки)
Прикреплённые документы
Вы можете просмотреть любой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, поэтому для закрытия документа пожалуйста не используйте кнопку «Назад» браузера.

Работа с текстовыми строками является важной составляющей обработки информации с помощью Excel. С ними удобно работать вручную, если их общее количество относительно небольшое. Но как только число строк становится приличным (например, одна или даже несколько сотен), то операции с ними становятся довольно затруднительными.
Существует несколько методов, как сделать это. Прежде всего, это можно сделать с помощью текстовых функций. Самая популярная из них – ПСТР, но на самом деле их значительно больше. С их помощью можно реализовать почти любую задумку, которую придумает мозг или же будет поставлена руководством на работе.
Также возможно использование макросов для достижения этой цели. Для этого в VBA существует специальная функция – Split. Она разделяет строку по разделителям, в качестве которого может выступать как определенный символ, так и сразу несколько. Синтаксис функции включает три аргумента, из которых обязательным является только один.
Compare. С помощью этого аргумента функции передается тип сравнения – двоичный или текстовый. Простыми словами, в первом случае (если тип сравнения установлен на 0), функция учитывает регистр букв при сравнении. В случае же текстового сравнения регистр букв не учитывается.
Значение, которое эта функция вернет – массив, в котором перечислены подстроки, число которых задается параметром limit. Как могло заинтересовать наблюдательного читателя, если поставить значение -1, то функцией будут возвращены все подстроки. И теперь давайте приведем несколько примеров, как работает эта VBA функция.
Здесь используются значения аргумента Delimiter в –, а Limit – 2. Таким образом, всего возможно была разбивка на три части, но поскольку мы указали только две, то видим, итоговый результат тоже являет собой одну подстроку «vremya» и одну подстроку «ne-zhdet». Видим, что все на самом деле невероятно просто.
БАТТЕКСТ(Значение). Функция, необходимая для превращения ячейки числового формата в текстовый. Ее полезно использовать, если формула требует текстового значения, в то время как в ячейке число представлено в виде цифрового. С помощью данной функции можно конвертировать данные из одного типа в другой.
ЗАМЕНИТЬ(Старый текст, Начальная позиция, число знаков, новый текст). С помощью этой функции можно заменить один текст на другой, в качестве ориентира используя определенное количество знаков, начиная с позиции, которая указана пользователем.
ПРАВСИМВ(Строка, Количество знаков). Принцип работы этой функции аналогичный, только с ее помощью можно вернуть определенное количество знаков справа. То есть, узнать, какой будет часть строки, начиная с самого последнего символа.
НАЙТИ(текст для поиска, текст, в котором ищем, начальная позиция). С помощью этой функции можно получить позицию, на которой находится текст, заданный пользователем. Этот оператор можно использовать, только если регистр для нас важен. Если же нет разницы, какие буквы использовать: большие или маленькие, то есть аналогичная функция – ПОИСК. Также следует отметить, что эта функция будет возвращать исключительно первое вхождение, все последующие уже не берутся в учет. Для этого существуют другие функции.
ПОДСТАВИТЬ (текст, старый текст, новый текст, позиция). Это очень интересная функция. В чем-то она схожа на оператор ЗАМЕНИТЬ, но имеет более широкий функционал. Если пользователь не указал последний аргумент, то замена осуществляется всех вхождений в тексте. Таким образом, это позволяет автоматизировать опции Excel «Заменить все».
ПСТР (Текст, Начальная позиция, Количество знаков). Это одна из самых главных функций, которую мы сегодня будем разбирать очень подробно. Она в чем-то имеет схожий принцип на ЛЕВСИМВ, только дает возможность начать поиск подстроки не с самого начала, а с определенной позиции.
Принцип многих этих функций схож. Поэтому когда вы изучите одну из них, будет значительно проще выучить следующие. А когда начать их применять на практике, то они будут выучены автоматически. Давайте опишем реальный пример, как можно использовать текстовые функции.
Давайте опишем несколько практических применений текстовых функций. Для наглядности, мы представим работу функции ПОДСТРОКА и задачу, которую нужно решить. Первая колонка этой таблицы – полная строка. Вторая – значение, которое нам нужно найти в первой колонке. В третьем столбце перечислены формулы, с помощью которых это можно сделать.
Функция ПСТР в Excel используется наиболее часто, чтобы достать часть строки и использовать ее в дальнейших вычислениях или же просто записать в ячейке. Причина популярности этой функции проста – когда есть большой объем информации, который был импортирован с других программ, то нередко приходится доставать часть из нее в ручном режиме. А с помощью этой функции можно хоть немного автоматизировать процесс. Давайте разберем эту функцию более подробно.
Она предусматривает использование трех аргументов, каждый из которых является обязательным: текст, который обрезается, откуда начинать обрезку и где заканчивать. В качестве источника данных для обработки может быть текст, написанный в ячейке таблицы, а также тот, который был сгенерирован другой формулой. Так как нам нужно достать подстроку, то необходимо указать следующие аргументы:
Текст. Текстовая строка, из которой мы будем получать «обрезанный» вариант. Кроме результата работы функции и ссылки на ячейку, в качестве параметра этого аргумента может также выступать и непосредственно текстовая константа. Но на практике ее использование лучше всего подходит для тренировки. В реальной жизни в этом нет необходимости, поскольку всегда можно вручную вставить нужный фрагмент текста в любую ячейку.
Число знаков. Здесь записывается итоговое количество знаков, которые нам надо отсчитать с начальной позиции. Минимальное значение – 1. Чисто гипотетически есть возможность указать в качестве значения этого аргумента 0, но в этом случае в качестве результата будет получена пустая строка.
Есть еще один вариант этой формулы: ПСТРБ, которая позволяет работать с мультибайтовыми строками. Но в нашем языке таких нет, поэтому достаточно просто знать о том, что такая формула есть. Возможных результата после работы этой формулы два:
Если, даже несмотря на то, что значение «начальная позиция» меньше итоговой длины строки, сумма значений «начальная позиция» и «число знаков» больше, чем общее количество знаков этого текста, то функцией возвращаются оставшиеся знаки, начиная с той позиции, которая указана. Таким образом, можно указать в качестве числа знаков заведомо большое число, чтобы функция вернула те символы, которые расположены справа до самого конца строки.
Давайте рассмотрим небольшой пример того, как можно выделить отдельные знаки из строки с помощью функции ПСТР. Приведем очень простую ситуацию. Предположим у нас в ячейке B14 записана простая строка, состоящая из последовательности чисел от 1 до 0. Предположим, нам надо из строки 1234567890 получить тройку. В таком случае формула должна быть такой: =ПСТР(B14;3;1).
Простыми словами, мы говорим программе, что из этой последовательности значений нужно достать один знак, стартуя третьим числом в этой строке. Несмотря на то, что она кажется числовой, в нашем примере она текстовая. После того, как мы дали эти команды программе, на выходе мы получим цифру 3. Когда же может понадобиться умение решать именно такую задачу? Прежде всего, когда у нас есть набор символов, содержащийся в одной строке, и нам нужно брать оттуда определенные знаки.
MID — это одна из функций текста, которую Microsoft Excel предоставляет для работы с текстовыми строками. На самом базовом уровне он используется для извлечения подстроки из середины текстовой строки. В этом руководстве мы обсудим синтаксис и особенности функции Excel MID, а затем вы узнаете о нескольких творческих способах ее использования для выполнения сложных задач.
Как использовать MID в Excel — примеры формул
Извлечь имя и фамилию
Получить подстроку между двумя разделителями

Извлечь N-е слово из текстовой строки
Вытяните слово, содержащее определенные символы
Как заставить Excel Mid возвращать число
Вообще говоря, функция MID в Excel предназначена для извлечения подстроки из середины исходной текстовой строки. С технической точки зрения, функция MID возвращает указанное количество символов, начиная с указанной позиции.
-й символ, используйте эту формулу:
=MID(A2,8, 7)
Результат может выглядеть примерно так:
5 вещей, которые вам следует знать о функции Excel MID
Как у вас только что видел, нет ничего сложного в использовании функции MID в Excel. А знание следующих простых фактов убережет вас от наиболее распространенных ошибок.
Функция MID всегда возвращает текстовую строку , даже если извлеченная подстрока содержит только цифры. Это может иметь решающее значение, если вы хотите использовать результат своей формулы Mid в других вычислениях. Чтобы преобразовать вывод в число, используйте MID в сочетании с функцией VALUE, как показано в этом примере.
Если start_num больше общей длины исходного текста, формула Excel Mid возвращает пустую строку («»).

Если start_num меньше 1, формула Mid возвращает #VALUE! ошибка.
Если num_chars меньше 0 (отрицательное число), формула Mid возвращает #VALUE! ошибка. Если num_chars равно 0, выводится пустая строка (пустая ячейка).
Если сумма start_num и num_chars превышает общую длину исходной строки, функция Excel MID возвращает подстроку, начинающуюся с start_num и до последнего символа.
Функция MID в Excel — примеры формул
При работе с реальными задачами в Excel вам чаще всего потребуется использовать MID в сочетании с другими функциями, как показано в следующих примерах.
Если у вас была возможность прочитать наши последние руководства, вы уже знаете, как получить имя с помощью функции ВЛЕВО и получить фамилию с помощью функции ПРАВО. Но, как это часто бывает в Excel, одно и то же можно сделать разными способами.
Формула MID для получения имени
Предполагая, что полное имя находится в ячейке A2, имя и фамилия разделены пробелом, вы можете получить имя, используя эту формулу:
=MID(A2,1, ПОИСК(" ",A2)-1)
Функция ПОИСК используется для поиска в исходной строке символа пробела (» «) и возврата его позиции, из которой вы вычитаете 1, чтобы избежать пробелов в конце. Затем вы используете функцию MID, чтобы вернуть подстроку, начинающуюся с первого символа и заканчивающуюся символом, предшествующим пробелу, таким образом извлекая первое имя.
Формула MID для получения фамилии
Чтобы извлечь фамилию из A2, используйте следующую формулу:
=TRIM(MID(A2,SEARCH(" ",A2),LEN(A2)))
Опять же, вы используете функцию ПОИСК, чтобы определить начальную позицию (пробел). Нам не нужно точно вычислять конечную позицию (как вы помните, если сумма start_num и num_chars больше, чем общая длина строки, возвращаются все оставшиеся символы). Так, в num_chars , вы просто указываете общую длину исходной строки, возвращаемой функцией LEN. Вместо LEN вы можете поместить число, представляющее самую длинную фамилию, которую вы ожидаете найти, например 100. Наконец, функция TRIM удаляет лишние пробелы, и вы получаете следующий результат:
Как получить подстроку между двумя разделителями
Продолжая предыдущий пример, если помимо имени и фамилии ячейка A2 также содержит отчество, как его извлечь?
Технически задача сводится к определению положения двух пробелов в исходной строке, и вы можете сделать это следующим образом:
Как и в предыдущем примере, используйте функцию ПОИСК, чтобы определить положение первый пробел («»), к которому вы добавляете 1, потому что хотите начать с символа, следующего за пробелом.
Таким образом, вы получаете аргумент start_num вашей формулы Mid: ПОИСК(» «,A2)+1
Далее получаем позицию 2 ^nd символ пробела с помощью вложенных функций поиска, которые предписывают Excel начать поиск с 2 ^nd появления пробела: SEARCH(» «,A2,SEARCH(» «,A2)+1)
Чтобы узнать количество возвращаемых символов, вычтите позицию пробела 1 ^st из позиции пробела 2 ^nd, а затем вычтите 1 из результата, так как вам не нужны лишние пробелы в строке. результирующая подстрока. Таким образом, у вас есть аргумент num_chars : ПОИСК (» «, A2, ПОИСК (» «,A2)+1) — ПОИСК (» «,A2)
Со всеми аргументами вместе получается формула Excel Mid для извлечения подстроки между двумя пробелами:
=MID(A2, ПОИСК(» «,A2)+1, ПОИСК (» «, A2, ПОИСК (» «,A2)+1) — ПОИСК (» «,A2)-1)
На следующем снимке экрана показан результат:

Аналогичным образом вы можете извлечь подстроку между любыми другими разделителями:
MID( строка , ПОИСК( разделитель , строка )+1, ПОИСК( разделитель , строка , ПОИСК ( разделитель , строка )+1) — ПОИСК ( разделитель , строка )-1)
и пробел, используйте эту формулу:
=MID(A2,ПОИСК(«, «,A2)+1,ПОИСК(«, «,A2,ПОИСК(«, «,A2)+1)-ПОИСК(» , «,A2)-1)
На следующем снимке экрана эта формула используется для извлечения состояния и отлично справляется со своей задачей:
Этот пример демонстрирует изобретательское использование сложной формулы Mid в Excel, которая включает 5 различных функций:
ДЛСТР — для получения общей длины строки.
ПОВТОР — повтор определенного символа заданное количество раз.
ПОДСТАВИТЬ — заменить один символ другим.
MID — извлечь подстроку.
TRIM — удалить лишние пробелы.
Общая формула выглядит следующим образом:
TRIM(MID(SUBSTITUTE( string ,» «,REPT(» «,LEN( строка ))), ( N -1)*LEN( строка )+1, LEN( строка )))
Где:
Строка — это исходная текстовая строка, из которой вы хотите извлечь нужное слово.
N — количество слов, которые необходимо извлечь.
Например, чтобы извлечь 2 ^-е слово из строки в ячейке A2, используйте следующую формулу: (2-1)*ДЛСТР(A2)+1, ДЛСТР(A2)))
Или вы можете ввести номер слова для извлечения (N) в какую-либо ячейку и указать ссылку на эту ячейку в своей формуле, как показано на снимке экрана ниже:
Как работает эта формула
По сути, формула оборачивает каждое слово в исходной строке множеством пробелов, находит нужный блок «пробелы-слова-пробелы», извлекает его, а затем удаляет лишние пробелы. Чтобы быть более точным, формула работает со следующей логикой:
Приведенная выше формула прекрасно работает в большинстве ситуаций. Однако, если их 2 или более последовательных пробела между словами дает неправильный результат. Чтобы исправить это, вложите другую функцию TRIM внутрь SUBSTITUTE, чтобы удалить лишние пробелы между словами, за исключением одного пробела между словами, например:
= TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))
На следующем снимке экрана показана улучшенная формула в действии:
Если ваши исходные строки содержат несколько пробелы между словами, а также очень большие и очень маленькие слова, дополнительно вставьте функцию TRIM в каждый LEN, просто чтобы быть в безопасности:
=ОТРЕЗАТЬ(СРЕДНЯЯ(ЗАМЕНИТЬ(ОТРЕЗАТЬ(A2)," ",ПОВТОР(" ",ДЛСТР(ОТРЕЗАТЬ(A2)))), (B2-1)*ДЛСТР(ОТРЕЗАТЬ(A2))+1, ДЛСТР) (TRIM(A2))))
Я согласен, что эта формула выглядит немного громоздкой, но она безукоризненно обрабатывает все виды строк.
Как извлечь слово, содержащее определенные символы
В этом примере показана еще одна нетривиальная формула Excel Mid, которая извлекает слово, содержащее определенные символы, из любой точки исходной текстовой строки:
TRIM(MID (ЗАМЕНИТЬ( строка , » «, ПОВТОР (» «, 99)), МАКС (1, НАЙТИ ( символов , ПОДСТАВИТЬ ( строка , » «, ПОВТОР (» «, 99)))-50), 99) )
Предположим, что исходный текст находится в ячейке A2, и вы хотите получить подстроку, содержащую символ «$» (цена), формула примет следующий вид:
=TRIM(MID(SUBSTITUTE(A2, " ",ПОВТОР(" ",99)),МАКС(1,НАЙТИ("$",ЗАМЕНИТЬ(A2," ",ПОВТОР(" ",99)))-50),99))
В аналогичным образом вы можете извлекать адреса электронной почты (на основе символа «@»), имена веб-сайтов (на основе «www») и так далее.
Как работает эта формула
Как и в предыдущем примере, функции ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходной текстовой строке в несколько пробелов, точнее, в 99 пробелов.
Функция НАЙТИ находит позицию нужного символа ($ в этом примере), из которой вы вычитаете 50. Это возвращает вас на 50 символов назад и помещает где-то в середину блока из 99 пробелов, который предшествует подстроке, содержащей указанный характер.
Функция MAX используется для обработки ситуации, когда нужная подстрока находится в начале исходной текстовой строки. В этом случае результатом FIND()-50 будет отрицательное число, и MAX(1, FIND()-50) заменит его на 1.
С этой начальной точки функция MID собирает следующие 99 символов и возвращает интересующую подстроку, окруженную множеством пробелов, например: пробелов-подстроки-пробелов . Как обычно, функция TRIM помогает убрать лишние пробелы.
Совет. Если извлекаемая подстрока очень большая, замените 99 и 50 большими числами, скажем, 1000 и 500.
Как заставить формулу Excel Mid возвращать число
Как и другие текстовые функции, Excel MID всегда возвращает текстовую строку , даже если он содержит только цифры и очень похож на число. Чтобы преобразовать вывод в число, просто «деформируйте» формулу Mid в функцию VALUE, которая преобразует текстовое значение, представляющее число, в число.
Например, чтобы извлечь 3-символьную подстроку, начинающуюся с 7 ^{-й символ} и преобразовать его в число, используя эту формулу:
=ЗНАЧ(MID(A2,7,3))
На снимке экрана ниже показан результат. Обратите внимание, что числа, выровненные по правому краю, перенесены в столбец B, в отличие от исходных текстовых строк, выровненных по левому краю в столбце A:
Тот же подход работает и для более сложных формул. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их, используя формулу Mid, которая получает подстроку между двумя разделителями, вложенными в функцию VALUE:
=ЗНАЧ(СРЕДН(A2,ПОИСК(":",A2)+1,ПОИСК(":",A2,ПОИСК(":",A2)+1)-ПОИСК(":",A2)- 1))
Вот как вы используете функцию MID в Excel. Чтобы лучше понять формулы, обсуждаемые в этом руководстве, вы можете скачать образец книги ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Загрузить учебное пособие
Функция MID в Excel — примеры формул (файл .xlsx)
Дополнительные примеры использования функции MID в Excel:
Функции ПОИСК и ПОИСК в Excel с примерами формул
В учебном пособии объясняется синтаксис функций ПОИСК и ПОИСК в Excel и приводятся примеры формул для расширенного нетривиального использования.
В прошлой статье мы рассмотрели основы диалогового окна «Найти и заменить» в Excel. Однако во многих ситуациях вам может понадобиться, чтобы Excel автоматически находил и извлекал данные из других ячеек на основе ваших критериев. Итак, давайте подробнее рассмотрим, что могут предложить функции поиска Excel.
Функция ПОИСК Excel
Функция ПОИСК Excel
Различия между функциями НАЙТИ и ПОИСК
Как использовать НАЙТИ и ПОИСК в Excel — примеры формул
Поиск строки, предшествующей заданному символу или следующей за ним
Найти N-е вхождение данного символа в ячейку
Извлечь N символов после определенного символа
Поиск и извлечение текста между скобками
Функция ПОИСК Excel
Функция НАЙТИ в Excel используется для возврата позиции определенного символа или подстроки в текстовой строке.
Синтаксис функции поиска в Excel следующий:
НАЙТИ(найти_текст, внутри_текста, [начальный_номер])
Первые 2 аргумента обязательны, последний необязателен.
Find_text — символ или подстрока, которую вы хотите найти.
Within_text — текстовая строка для поиска. Обычно он предоставляется как ссылка на ячейку, но вы также можете ввести строку непосредственно в формулу.
Start_num — необязательный аргумент, указывающий, с какого символа начинать поиск. Если опущено, поиск начинается с 1 ^st символа строки внутри_текста.
Если функция НАЙТИ не находит символ(ы) find_text, возникает ошибка #ЗНАЧ! возвращается ошибка.
Например, формула =НАЙТИ("d", "найти") возвращает 4, поскольку «d» является 4-й ^-й-й буквой в слове « найти «. Формула =НАЙТИ("а", "найти") возвращает ошибку, потому что в « найти » нет «а».
Функция ПОИСК в Excel — о чем нужно помнить!
Чтобы правильно использовать формулу НАЙТИ в Excel, имейте в виду следующие простые факты:
Функция НАЙТИ чувствительна к регистру . Если вы ищете совпадение без учета регистра, используйте функцию ПОИСК.
Функция НАЙТИ в Excel не позволяет использовать подстановочных знака .
Если аргумент find_text содержит несколько символов, функция НАЙТИ возвращает позицию 9 символов.0034 первый символ . Например, формула НАЙТИ(«ап»,»счастливый») возвращает 2, потому что «а» в 2-й ^-й-й букве в слове «счастливый».
Если внутри_текста содержится несколько вхождений find_text, возвращается первое вхождение. Например, FIND(«l», «hello») возвращает 3, что является позицией первого символа «l» в слове «hello».
Если find_text представляет собой пустую строку «», формула Excel НАЙТИ возвращает первый символ в строке поиска.
Функция НАЙТИ в Excel возвращает значение #ЗНАЧ! ошибка , если происходит одно из следующих событий:
Find_text не существует в пределах_текста.
Start_num содержит больше символов, чем внутри_текста.
Start_num равен 0 (ноль) или отрицательному числу.
Функция ПОИСК в Excel
Функция ПОИСК в Excel очень похожа на НАЙТИ тем, что она также возвращает местоположение подстроки в текстовой строке. Синтаксис и аргументы аналогичны FIND: 9.0003
ПОИСК(найти_текст, внутри_текста, [начальный_номер])
В отличие от НАЙТИ, функция ПОИСК нечувствительна к регистру и позволяет использовать подстановочные знаки, как показано в следующем примере.
Вот несколько основных формул ПОИСКА в Excel:
=ПОИСК("рынок", "супермаркет") возвращает 6, потому что подстрока «рынок» начинается с 6-го ^-го-го символа слова «супермаркет».
=ПОИСК("e", "Excel") возвращает 1, так как «e» является первым символом в слове «Excel» без учета регистра.
Как и НАЙТИ, функция ПОИСК Excel возвращает #ЗНАЧ! ошибка, если:
Значение аргумента find_text не найдено.
Аргумент start_num больше, чем длина внутри_текста.
Start_num равен или меньше нуля.
Далее в этом руководстве вы найдете еще несколько содержательных примеров формул, которые демонстрируют, как использовать функцию ПОИСК на листах Excel.
ПОИСК в Excel и ПОИСК в Excel
Как уже упоминалось, функции ПОИСК и ПОИСК в Excel очень похожи с точки зрения синтаксиса и использования. Однако у них есть пара отличий.
1. ПОИСК с учетом регистра и ПОИСК без учета регистра
Наиболее существенное различие между функциями ПОИСК и НАЙТИ в Excel заключается в том, что ПОИСК не чувствителен к регистру, а ПОИСК — с учетом регистра.
Например, ПОИСК(«e», «Excel») возвращает 1, поскольку игнорирует регистр «E», а ПОИСК(«e», «Excel») возвращает 4, поскольку учитывает регистр.
2. Поиск с подстановочными знаками
В отличие от НАЙТИ, функция ПОИСК Excel принимает подстановочные знаки в аргументе искомый_текст:
Знак вопроса (?) соответствует одному символу, а
Звездочка (*) соответствует любой последовательности символов.
Чтобы увидеть, как это работает с реальными данными, рассмотрим следующий пример:
Как видно на снимке экрана выше, формула ПОИСК(«функция*2013», A2) возвращает позицию первого символа («f» ) в подстроке, если текстовая строка, указанная в аргументе inside_text, содержит как «функция», так и «2013», независимо от того, сколько других символов находится между ними.
Совет. Чтобы найти вопросительный знак (?) или звездочку (*), введите тильду (~) перед соответствующим символом.
Примеры формул ПОИСК и ПОИСК в Excel
На практике функции ПОИСК и ПОИСК в Excel редко используются сами по себе. Как правило, вы должны использовать их в сочетании с другими функциями, такими как MID, LEFT или RIGHT, и следующие примеры формул демонстрируют некоторые примеры их использования в реальной жизни.
Пример 1. Найти строку, предшествующую или следующую за заданным символом
В этом примере показано, как можно найти и извлечь все символы в текстовой строке слева или справа от определенного символа. Чтобы упростить понимание, рассмотрим следующий пример.
Предположим, у вас есть столбец имен (столбец A), и вы хотите вывести Имя и Фамилию в отдельные столбцы.
Чтобы получить имя, вы можете использовать НАЙТИ (или ПОИСК) в сочетании с функцией ЛЕВЫЙ:
=ЛЕВЫЙ(A2, НАЙТИ(" ", A2)-1)
или
=ЛЕВЫЙ(A2,ПОИСК(" ",A2)-1)
Как вы, наверное, знаете, функция Excel ВЛЕВО возвращает указанное количество крайних левых символов в строке. И вы используете функцию НАЙТИ, чтобы определить позицию пробела («»), чтобы сообщить функции ВЛЕВО, сколько символов нужно извлечь. При этом вы вычитаете 1 из позиции пробела, потому что не хотите, чтобы возвращаемое значение включало пробел.
Для извлечения фамилии используйте комбинацию функций ПРАВО, НАЙТИ/ПОИСК и ДЛСТР. Функция ДЛСТР нужна для получения общего количества символов в строке, из которой вы вычитаете позицию пробела:
=ВПРАВО(A2,ДЛСТР(A2)-НАЙТИ(" ",A2))
или
=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(" ",A2))
следующий снимок экрана демонстрирует результат:
Для более сложных сценариев, таких как извлечение отчества или разделение имен с суффиксами, см. Разделение ячеек в Excel с помощью формул.
Пример 2. Найти N-е вхождение заданного символа в текстовую строку
Предположим, что у вас есть несколько текстовых строк в столбце A, скажем, список артикулов, и вы хотите найти положение 2 ^nd тире в строке. Следующая формула отлично работает:
=НАЙТИ("-", A2, НАЙТИ("-",A2)+1)
Первые два аргумента легко интерпретировать: найти тире («-«) в ячейке А2. В третий аргумент (start_num) вы встраиваете еще одну функцию НАЙТИ, которая сообщает Excel, что поиск нужно начинать с символа, следующего за первым вхождением тире (НАЙТИ(«-«,A2)+1).
Чтобы вернуть позицию 3 ^rd вхождения , вы вставляете приведенную выше формулу в аргумент start_num другой функции НАЙТИ и добавляете 2 к возвращаемому значению:
=НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-",A2 )+1) +2)
Другой и, вероятно, более простой способ найти N-е вхождение заданного символа — использовать функцию Excel НАЙТИ в сочетании с СИМВОЛОМ и ПОДСТАВИТЬ:
=НАЙТИ(СИМВОЛ(1), ПОДСТАВИТЬ(A2,"-",CHAR(1),3))
Где «-» — это рассматриваемый символ, а «3» — это N-е вхождение, которое вы хотите найти.
В приведенной выше формуле функция ПОДСТАВИТЬ заменяет третье вхождение дефиса («-«) на СИМВОЛ(1), который является непечатаемым символом «Начало заголовка» в системе ASCII. Вместо CHAR(1) вы можете использовать любой другой непечатаемый символ от 1 до 31. Затем функция НАЙТИ возвращает позицию этого символа в текстовой строке. Итак, общая формула выглядит следующим образом:
НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ( ячейка , символ ,СИМВОЛ(1), N-е вхождение ))
На первый взгляд может показаться, что приведенные формулы не имеют большого практического значения, но следующий пример покажет, насколько они полезны при решении реальных задач.
Примечание. Помните, что функция НАЙТИ в Excel чувствительна к регистру. В нашем примере это не имеет значения, но если вы работаете с буквами и хотите найти совпадение без учета регистра , используйте функцию ПОИСК вместо НАЙТИ.
Пример 3. Извлечь N символов после определенного символа
Чтобы найти подстроку заданной длины в любой текстовой строке, используйте ПОИСК или ПОИСК в Excel в сочетании с функцией СРЕДН. В следующем примере показано, как можно использовать такие формулы на практике.
В нашем списке SKU, предположим, вы хотите найти первые 3 символа после первого дефиса и вытащить их в другой столбец.
Если группа символов, предшествующая первому тире, всегда содержит одинаковое количество элементов (например, 2 символа), это будет тривиальной задачей. Вы можете использовать функцию MID для возврата 3 символов из строки, начиная с позиции 4 (пропуская первые 2 символа и тире):
=MID(A2, 4, 3)
В переводе на английский язык формула гласит: «Посмотрите в ячейку A2, начните извлечение с символа 4 и верните 3 символа».
Однако в реальных рабочих листах подстрока, которую нужно извлечь, может начинаться в любом месте текстовой строки. В нашем примере вы можете не знать, сколько символов предшествует первому тире. Чтобы справиться с этой проблемой, используйте функцию НАЙТИ, чтобы определить начальную точку подстроки, которую вы хотите получить.
Формула НАЙТИ для возврата позиции тире 1 ^st выглядит следующим образом:
=НАЙТИ("-",A2)
Поскольку вы хотите начать с символа, следующего за тире, добавьте 1 к возвращаемому значению и вставьте указанную выше функцию во второй аргумент (start_num) функции MID:
=MID(A2, FIND("-",A2)+1, 3)
В этом сценарии Функция ПОИСК в Excel работает одинаково хорошо:
=СРЕДН(A2, ПОИСК("-",A2)+1, 3)
Это здорово, но что, если группа символов после первого тире содержит другое количество символов? Хм… это может быть проблемой:
Как вы видите на скриншоте выше, формула отлично работает для строк 1 и 2. В строках 4 и 5 вторая группа содержит 4 символа, но только первые 3 символа возвращаются. В строках 6 и 7 во второй группе всего 2 символа, и поэтому наша формула поиска в Excel возвращает после них тире.
Если вы хотите вернуть все символы между 1 ^st и 2 ^nd вхождениями определенного символа (тире в этом примере), как бы вы поступили? Вот ответ:
=СРЕД(A2, НАЙТИ("-",A2)+1, НАЙТИ("-", A2, НАЙТИ("-",A2)+1) - НАЙТИ("-", A2)-1)
Для лучшего понимания этой формулы MID рассмотрим ее аргументы один за другим:
1 ^st аргумент (текст). Это текстовая строка, содержащая символы, которые вы хотите извлечь, ячейка A2 в этом примере.
2 ^nd аргумент (start_position). Определяет позицию первого символа, который вы хотите извлечь. Вы используете функцию НАЙТИ, чтобы найти первый дефис в строке и добавить 1 к этому значению, потому что вы хотите начать с символа, следующего за дефисом: НАЙТИ(«-«,A2)+1.
3 ^rd аргумент (num_chars). Указывает количество символов, которое вы хотите вернуть. В нашей формуле это самая сложная часть. Вы используете две функции НАЙТИ (или ПОИСК), одна из которых определяет позицию первого тире: НАЙТИ («-«, A2). А другой возвращает позицию второго тире: НАЙТИ(«-«, A2, НАЙТИ(«-«,A2)+1). Затем вы вычитаете первое из второго, а затем вычитаете 1, потому что вы не хотите включать ни одно тире. В результате вы получите количество символов между 1 ^st и 2 ^nd тире, это именно то, что мы ищем. Итак, вы передаете это значение аргументу num_chars функции MID.
Аналогичным образом можно вернуть 3 символа после дефиса 2 ^и:
=MID(A2, НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-", A2)+1) +2), 3)
Или извлеките все символы между тире 2 ^nd и 3 ^rd:
=MID(A2, FIND("-", A2, FIND ("-",A2)+1)+1, НАЙТИ("-",A2, НАЙТИ("-", A2, НАЙТИ("-",A2)+1) +2) - НАЙТИ("-", A2, НАЙТИ("-",A2)+1)-1)
Пример 4. Поиск текста в скобках
Предположим, у вас есть длинная текстовая строка в столбце A, и вы хотите найти и извлечь только текст, заключенный в скобки.
Для этого вам понадобится функция MID, чтобы вернуть желаемое количество символов из строки, и функция Excel FIND или SEARCH, чтобы определить, с чего начать и сколько символов нужно извлечь.
=СРЕДН(A2,ПОИСК("(",A2)+1, ПОИСК(")",A2)-ПОИСК("(",A2)-1)
Логика этой формулы аналогична тем, которые мы обсуждали в предыдущем примере. И снова самая сложная часть — это последний аргумент, который сообщает формуле, сколько символов нужно вернуть.

ПСТР в Excel (функция получения подстроки) | MS Office

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