Функция НАЙТИ
Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».
Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; )
- искомый_текст – строка, которую необходимо найти;
- текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
- нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.
Пример использования:
Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.
Как извлечь текст из ячейки с помощью Ultimate Suite
Как выделить подстроку между двумя разделителями.
Функция НАЙТИ
Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».
Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; )
- искомый_текст – строка, которую необходимо найти;
- текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
- нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.
Пример использования:
Из отрывка стихотворения великого поэта С.А.Есенина находим вхождение первого символа строки «птица». Поиск осуществляется с начала строки. Если в приведенном примере поиск осуществлялся бы с 40 символа, то функция в результате вернула ошибку, т.к. позиции вхождения не было найдено.
Как посчитать возраст по дате рождения в Excel?
Пример 3. Таблица содержит данные о сотрудниках в столбцах имени и даты рождения. Создайте столбец, в котором будут отображаться фамилия и возраст сотрудника в формате «Иванов — 27».
Чтобы вернуть строку с фамилией и текущим возрастом, мы используем следующую формулу:
Функция MID возвращает часть строки перед пробелом, положение которой определяется функцией НАЙТИ. Для определения возраста сотрудника используется функция ДАННЫЕ, результирующее значение которой обрезается до ближайшего меньшего целого числа, чтобы получить количество полных лет. Функция ТЕКСТ преобразует полученное значение в текстовую строку.
Для соединения (конкатенации) результирующих строк используйте символы «&». Типичные ошибки при использовании функции MID
При использовании функции MID возможны ошибки при неправильном синтаксисе или нарушении некоторых фундаментальных правил:
Вы не можете записать отрицательное значение в качестве аргумента для этой функции
В этом случае будет отображена ошибка #VALUE.
важно следить за тем, чтобы начальная позиция не превышала желаемую длину строки. В противном случае будет возвращено пустое значение.
Пример 3: Перечисляем весь алфавит
Используя инструмент автозаполнения в Excel Вы можете написать два числа и быстро создать всю последовательность чисел. К сожалению, это не работает с буквами. Тем не менее, Вы можете использовать функцию CHAR (СИМВОЛ), чтобы создать список всех букв алфавита.
Используйте тот же приём с добавлением 64 к номеру символа, чтобы получить буквы алфавита.
Создаем пользовательский список
Если Вы хотите вводить алфавит при помощи автозаполнения, есть возможность импортировать список букв алфавита в пользовательский список.
- Скопируйте ячейки с буквами алфавита и вставьте их в отдельную строку как значения.
- Выберите эти ячейки. Здесь главное не перепутать и выбрать ячейки именно со значениями, а не с функцией CHAR (СИМВОЛ).
- Нажмите кнопку Office (Офис) на Ленте Excel, затем выберите Excel Options (Параметры Excel). Если у Вас Excel 2010, то выберите Options (Настройки).
- В списке категорий нажмите Popular (Основные). Если Excel 2010, то Advanced (Дополнительно).
- Найдите и нажмите кнопку Edit Custom Lists (Изменить списки…)
- Нажмите кнопку Import (Импорт), затем ОК и закройте меню настроек.
- Теперь введите любую букву на листе Excel и используйте автозаполнение, чтобы завершить последовательность.
Текстовые функции (справка) — служба поддержки Office
Как разделить текст в MS Excel
Однако, как быть, если необходимо разделить ячейку в которой заведомо не известно количество символов, которые нужно отделить, а известно лишь сколько частей необходимо получить в результате операции. Самым простым примером такой ситуации может быть необходимость выделить из ячейки в которой занесено ФИО человека, отдельно фамилию, имя и отчество. Фамилии у всех разные, поэтому заранее узнать количество символов, которые необходимо отделить, не получится.
На Ваше обозрение представим два способа разделения текста. Один очень быстрый – для тех кому надо просто разделить текст заменив имеющийся, а второй с использованием формул.
Первый способ – супер быстрый
На самом деле в MS Excel существует встроенная возможность быстрого разделения текста в ячейке, если там присутствует или присутствуют разделитель/разделители (например, простой пробел или запятая). Причем таких разделителей может быть несколько, т.е. текст будет разделятся если в строке присутствует или пробел, или точка с запятой или запятая и т.д.
- Для этого необходимо выделить ячейки с текстом, который необходимо разделить и воспользоваться командой «Текст по столбцам».
- В англоязычной версии MS Excel данная команда звучит как «Text to Columns» вкладки «DATA».
- После несложных подсказок мастера (на самом деле, в нашем примере после выбора разделителя – пробела можно смело жать «Готово»)
- А вот и, собственно, результат.
Второй способ – с использованием формул
В такой ситуации понадобится сочетание функций: ПОИСК (SEARCH) и ПСТР (MID). Для начала, с помощью первой находим пробел между словами (между фамилией и именем и именем и отчеством), а потом подключаем вторую для того, чтобы выделить необходимое количество символов. Грубо говоря, первой функцией определяем количество символов, а второй – уже разделяем.
Кроме того, поскольку разделительного знака в конце строки нет, то количество символов в последнем слове (нашем случае – отчестве) вычислить не удастся, но это не проблема, достаточно указать заведомо бОльшее количество символов в качестве аргумента «число_знаков» функции ПСТР, например, 100.
Рассмотрим вышесказанное на примере. Сначала, для лучшего понимания, разнесем формулы и, таким образом, разделим весь процесс на два этапа.
Для того, чтобы определить количество символов, которые необходимо выделить в строке, необходимо определить позиции разделителей (в нашем случае пробела) их будет на один меньше нежели слов в ячейке.
Поскольку информация о количестве необходимых символах получена, следующим этапом будет использование функции ПСТР (MID).
Здесь, в качестве аргументов, используются промежуточные значения, полученные с помощью функции ПОИСК. Для последней колонки количество символов неизвестно, поэтому было взято заведомо бОльшее количество символов (в нашем случае 100).
Теперь попробуем соединить промежуточные расчеты в одну формулу.
Если текст в ячейке необходимо разделить лишь на две части, то необходимо произвести поиск лишь одного пробела (либо другого разделителя, который находится между словами), а для разделения на 4 и больше частей формулу придется усложнить поиском 3го, 4го и т.д. разделителей.
Найти позицию последнего появления символа в строке в Excel
Несколько дней назад коллега столкнулся с этой проблемой.
У него был список URL-адресов, как показано ниже, и ему нужно было извлечь все символы после последней косой черты («/»).
Так, например, с https://example.com/archive/Январь ему пришлось извлечь «январь».
Это было бы очень просто, если бы в URL-адресах была только одна косая черта.
У него был огромный список из тысяч URL-адресов разной длины и с различным количеством косых черт.
В таких случаях хитрость заключается в том, чтобы найти позицию последнего появления косой черты в URL-адресе.
В этом уроке я покажу вам два способа сделать это:
- Использование формулы Excel
- Использование пользовательской функции (созданной через VBA)
Получение последней позиции символа с помощью формулы Excel
Когда у вас есть позиция последнего вхождения, вы можете просто извлечь что-нибудь справа от него, используя функцию ВПРАВО.
Вот формула, которая находит последнюю позицию косой черты и извлекает весь текст справа от нее.
Как работает эта формула?
Давайте разберем формулу и объясним, как работает каждая ее часть.
- ЗАМЕНА (A2; ”/”,“”) — Эта часть формулы заменяет косую черту пустой строкой. Так, например, если вы хотите найти вхождение любой строки, кроме косой черты, используйте ее здесь.
- LEN (A2) -LEN (ЗАМЕНА (A2; ”/”,“”)) — Эта часть сообщит вам, сколько косых черт в строке. Он просто вычитает длину строки без косой черты из длины строки с косой чертой.
- ПОДСТАВИТЬ (A2, ”/”, ”@”, LEN (A2) -LEN (ПОДСТАВИТЬ (A2, ”/”, ””))) — Эта часть формулы заменит последнюю косую черту на @. Идея состоит в том, чтобы сделать этого персонажа уникальным. Вы можете использовать любой персонаж, какой захотите. Просто убедитесь, что он уникален и его еще нет в строке.
- НАЙТИ («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1) — Эта часть формулы даст вам позицию последней косой черты.
- LEN (A2) -FIND («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1) — Эта часть формулы сообщает нам, сколько символов стоит после последней косой черты.
- = ПРАВО (A2; LEN (A2) -НАЙТИ («@», ПОДСТАВИТЬ (A2, «/», «@», LEN (A2) -LEN (ПОДСТАВИТЬ (A2, «/», »»))), 1 )) — Теперь это просто даст нам строку после последней косой черты.
Получение последней позиции символа с помощью пользовательской функции (VBA)
Вышеупомянутая формула прекрасна и работает как шарм, но она немного сложна.
Если вам удобно использовать VBA, вы можете использовать настраиваемую функцию (также называемую функцией, определяемой пользователем), созданную с помощью VBA. Это может упростить формулу и сэкономить время, если вам придется делать это часто.
Давайте воспользуемся тем же набором данных URL-адресов (как показано ниже):
Для этого случая я создал функцию с именем LastPosition, которая находит последнюю позицию указанного символа (в данном случае это косая черта).
Вот формула, которая сделает это:
Вы можете видеть, что это намного проще, чем тот, который мы использовали выше.
Вот как это работает:
- LastPosition — наша настраиваемая функция — возвращает позицию косой черты. Эта функция принимает два аргумента — ссылку на ячейку с URL-адресом и символ, позицию которого нам нужно найти.
- Затем функция ВПРАВО дает нам все символы после косой черты.
Вот код VBA, создавший эту функцию:
Чтобы эта функция работала, вам нужно поместить ее в редактор VB. После этого вы можете использовать эту функцию, как любую другую обычную функцию Excel.
Вот шаги, чтобы скопировать и вставить этот код в серверную часть VB:
Вот шаги, чтобы поместить этот код в редактор VB:
- Перейдите на вкладку Разработчик.
- Выберите вариант Visual Basic. Это откроет редактор VB в бэкэнде.
- На панели Project Explorer в редакторе VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
- Перейдите во вкладку «Вставить» и нажмите «Модуль». Это вставит объект модуля для вашей книги.
- Скопируйте и вставьте код в окно модуля.
Теперь формула будет доступна на всех листах книги.
Обратите внимание, что вам необходимо сохранить книгу в формате .XLSM, поскольку в ней есть макрос. Кроме того, если вы хотите, чтобы эта формула была доступна во всех используемых вами книгах, вы можете сохранить ее в личной книге макросов или создать на ее основе надстройку
Вам также могут понравиться следующие руководства по Excel:
ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки
Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы
Для удобства также приводим ссылку на оригинал (на английском языке).
В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.
Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:
Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».
Можно также находить слова в других словах. Например, функция
возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.
Эти функции могут быть доступны не на всех языках.
Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Аргументы функций ПОИСК и ПОИСКБ описаны ниже.
Искомый_текст Обязательный. Текст, который требуется найти.
Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.
Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (
Если значение аргумента искомый_текст не найдено, #VALUE! возвращено значение ошибки.
Если аргумент начальная_позиция опущен, то он полагается равным 1.
Если Нач_позиция не больше 0 или больше, чем длина аргумента просматриваемый_текст , #VALUE! возвращено значение ошибки.
Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Чем отличается функция ПОИСК от функции НАЙТИ в Excel?
Функция ПОИСК очень схожа с функцией НАЙТИ по принципу действия. Более того у них фактически одинаковые аргументы. Только лишь названия аргументов отличаются, а по сути и типам значений – одинаковые:
Но опытный пользователь Excel знает, что отличие у этих двух функций очень существенные.
Отличие №1. Чувствительность к верхнему и нижнему регистру (большие и маленькие буквы). Функция НАЙТИ чувствительна к регистру символов. Например, есть список номенклатурных единиц с артикулом. Необходимо найти позицию маленькой буквы «о».
Теперь смотрите как ведут себя по-разному эти две функции при поиске большой буквы «О» в критериях поиска:
Отличие №2. В первом аргументе «Искомый_текст» для функции ПОИСК мы можем использовать символы подстановки для указания не точного, а приблизительного значения, которое должно содержаться в исходной текстовой строке. Вторая функция НАЙТИ не умеет использовать в работе символы подстановки масок текста: «*»; «?»; «~».
Для примера попробуем в этих же исходных строках столбца «наименования» найти приблизительный текст. Для этого укажем следующий вид критерия поиска используя символы подстановки: «н*ая».
Скачать примеры функций ПОИСК и НАЙТИ в Excel
Как видим во втором отличии функция НАЙТИ совершенно не умеет работать и распознавать спецсимволы для подстановки текста в критериях поиска при неточном совпадении в исходной строке.
Добавить значение в начало или конец ячеек формулой
Можно решить задачу формулой «СЦЕПИТЬ» или простой конкатенацией через амперсанд (символ «&»). Но на это нужно много времени, т.к. нужно
- добавить дополнительный столбец рядом с исходным
- ввести формулу с функцией «СЦЕПИТЬ» или формулу с амперсандом в первую ячейку — здесь кому что больше нравится. =СЦЕПИТЬ(A1;» слово») или A1&» слово».
- протянуть формулу до конца нового столбца, убедившись, что она затронула весь диапазон
- заменить в нем формулы на значения
- скопировать данные из нового столбца в первый
- удалить новый столбец
Ниже пример с добавлением одного символа, в данном случае — знака вопроса. Вместо него можно вписать внутри кавычек любое значение.
Могут возникнуть сложности с добавлением кавычек («) — поскольку формулы текстовые, аргумент указывается в кавычках, и во избежание ошибок одинарные кавычки обозначаются двойными, итого получается 4 знака подряд:
Если данную операцию приходится производить часто, такой процесс порядком поднадоест. Однажды надоел и автору надстройки для Excel !SEMTools, поэтому процесс был автоматизирован. Ниже перечислены несколько примеров, как добавить текст в каждую ячейку в Excel с ее помощью.
Функции подстроки Excel для извлечения текста из ячейки
В учебнике показано, как использовать функции подстроки в Excel для извлечения текста из ячейки, получения подстроки до или после указанного символа, найти ячейки, содержащие часть строки, и многое другое.
Прежде чем мы начнем обсуждать различные методы работы с подстроками в Excel, давайте уделим немного времени определению термина, чтобы мы могли начать с той же страницы. Итак, что такое подстрока? Проще говоря, это часть текстовой записи. Например, если вы наберете что-то вроде «AA-111» в ячейке, вы назовете это буквенно-цифровая строкаи любая часть строки, скажем, «AA», будет подстрока.
Хотя в Excel нет такой функции, как функция подстроки, существуют три текстовые функции (LEFT, RIGHT и MID) для извлечения подстроки заданной длины. Кроме того, есть функции НАЙТИ и ПОИСК для получения подстроки до или после определенного символа. И есть несколько других функций для выполнения более сложных операций, таких как извлечение чисел из строки, замена одной подстроки на другую, поиск частичного совпадения текста и т. д. Ниже вы найдете примеры формул для выполнения всего этого и многого другого. .
Динамические формулы с использованием функции НАЙТИ
Функция СЖПРОБЕЛЫ в Excel для удаления лишних пробелов в тексте
Как заменить звездочку «*» в Excel?
Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст. Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст. Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст
*» (явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:
?»
«
голоса
Рейтинг статьи
5 вариантов использования функции ИНДЕКС (INDEX)
Разбор текстовых строк в MS EXCEL
Извлекаем в EXCEL число из конца текстовой строки
Извлечем число из конца текстовой строки, например, из строки «Филатова123» получим «123».
Пусть текстовая строка Филатова123 находится в ячейке A1 . Чтобы извлечь число 123, расположенное справа, запишем формулу массива : =1*ПСТР(A1; ПОИСКПОЗ(ЛОЖЬ;ЕОШИБКА(1*ПСТР(A1;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР(A1)));1));0); 255)
Если число расположено в начале или середине значения, то формула работать не будет (см. файл примера ).
Общая формула, позволяющая извлечь число из начала, середины и конца текстовой строки посложнее и выглядит так:
О построении этой формулы смотрите здесь . Это также формула массива — не забудьте нажать CRTL+SHIFT+ENTER .
Как получить N-е слово из текста.
Этот пример демонстрирует первоначальное использование сложной формулы MID в Excel, которая включает 5 различных составных частей:
- DLSTR — чтобы получить общую длину.
- REPEAT — повторение заданного символа указанное количество раз.
- ЗАМЕНИТЬ — заменяет один символ другим.
- PSTR — извлекает подстроку.
- TRIMS — убирает лишние пробелы между словами.
Общая формула выглядит так:
Где:
- Строка — это исходный текст, из которого вы хотите извлечь желаемое слово.
- N — порядковый номер слова, которое нужно получить.
Например, чтобы получить второе слово из A2, используйте это выражение:
Или вы можете ввести порядковый номер слова, которое вы хотите извлечь (N), в ячейку и указать эту ячейку в формуле, как показано на снимке экрана ниже:
Как работает эта формула?
По сути, Excel оборачивает каждое слово в исходном тексте множеством пробелов, находит нужные пробелы-пробелы-слова, извлекает их, а затем удаляет лишние пробелы. Если быть более точным, это работает по следующей логике:
REPLACE и REPEAT заменяют каждый пробел в тексте дополнительными пробелами. Количество этих дополнительных вставок равно общей длине исходной строки: SUBSTITUTE ($ A $ 2; «»; REPEAT («»; DLSTR ($ A $ 2)))
Вы можете думать о промежуточном результате как о «астероидах» слов, дрейфующих в пространстве, например: word1-space-word2-space-word3 -… Эта длинная строка передается в текстовый аргумент MID.
- Затем определите начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * DLSTR (A1) +1. Это вычисление возвращает либо позицию первого символа первого слова, либо, чаще, позицию в n-й группе пробелов.
- Количество извлекаемых букв и цифр (второй аргумент) — это самая простая часть — просто возьмите исходную общую длину: DLSTR (A2).
- Наконец, TRIM устраняет начальный и конечный интервалы в извлеченном тексте.
Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если в строке между словами есть 2 или более пробелов, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию TRIM в SUBSTITUTE, чтобы удалить лишние пробелы между словами, оставив только одно, например:
На следующем рисунке показана улучшенная версия (2) в действии:
Если исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, также введите TRIM в каждый DLSTR, чтобы избежать ошибок:
Я согласен, что он выглядит немного загроможденным, но безупречно обрабатывает все параметры.
Особенности использования функции ПОИСКПОЗ в Excel
Извлечь подстроку до или после заданного символа
Как показано в приведенных выше примерах, функции Left, Right и Mid прекрасно справляются с однородными строками. Когда вы имеете дело с текстовыми строками переменной длины, потребуются более сложные манипуляции.
Примечание. Во всех приведенных ниже примерах мы будем использовать функцию SEARCH без учета регистра, чтобы получить позицию символа. Если вам нужна формула с учетом регистра, используйте функцию НАЙТИ.
Как извлечь текст перед определенным символом
Чтобы получить подстроку, предшествующую заданному символу, нужно сделать две вещи: сначала определить позицию интересующего символа, а затем вытянуть все символы перед ним. Точнее, вы используете функцию ПОИСК, чтобы найти положение символа, и вычитаете 1 из результата, потому что вы не хотите включать сам символ в вывод. А затем вы отправляете возвращенный номер непосредственно на num_chars аргумент ЛЕВОЙ функции:
Например, чтобы извлечь подстроку перед символом дефиса (-) из ячейки A2, используйте следующую формулу:
Независимо от того, сколько символов содержит ваша строка Excel, формула извлекает только текст до первого дефиса:
Как извлечь текст после символа
Чтобы получить текст после определенного символа, вы используете несколько иной подход: получите позицию символа с помощью ПОИСК или НАЙТИ, вычтите это число из общей длины строки, возвращаемой функцией ДЛСТР, и извлеките столько же символов из конца строки. Струна.
В нашем примере мы будем использовать следующую формулу для извлечения подстроки после первого дефиса:
=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(«-«,A2))
Как извлечь текст между двумя экземплярами символа
Чтобы получить подстроку между двумя вхождениями определенного символа, используйте следующую общую формулу:
Первые два аргумента этой формулы MID предельно ясны:
Текст это ячейка, содержащая исходную текстовую строку.
Start_num (начальная точка) — простая формула ПОИСК возвращает позицию нужного символа, к которой вы добавляете 1, потому что хотите начать извлечение со следующего символа.
Num_chars (количество символов для извлечения) — самая сложная часть:
- Во-первых, вы определяете положение второго вхождения символа, вкладывая одну функцию поиска в другую.
- После этого вы вычитаете позицию 1-го вхождения из позиции 2-го вхождения и вычитаете 1 из результата, так как вы не хотите включать символ-разделитель в результирующую подстроку.
Например, чтобы извлечь текст, окруженный двумя дефисами, вы должны использовать эту формулу:
=СРЕДН(A2, ПОИСК(«-«,A2) + 1, ПОИСК(«-«,A2,ПОИСК(«-«,A2)+1) — ПОИСК(«-«,A2) — 1)
На скриншоте ниже показан результат:
Если вы хотите извлечь текст между 2-м и 3-м или 3-м и 4-м вхождением одного и того же символа, вы можете использовать более компактную комбинацию ПОИСК ПОДСТАВКИ, чтобы получить позицию символа, как описано в разделе Как найти N-е вхождение символа в строку. :
В нашем случае мы могли бы извлечь подстроку между 2-м и 3-м дефисом по следующей формуле:
=MID(A2, НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(A2,»-«,СИМВОЛ(1),2))+1, НАЙТИ(СИМВОЛ(1),ЗАМЕНИТЬ(A2,»-«,СИМВОЛ(1) ,3)) — НАЙТИ(СИМВОЛ(1),ПОДСТАВИТЬ(A2,»-«,СИМВОЛ(1),2))-1)
Функция 18: SEARCH (ПОИСК)
Функция SEARCH (ПОИСК) ищет текстовую строку внутри другой текстовой строки, и, если находит, то сообщает её позицию.
Как можно использовать функцию SEARCH (ПОИСК)?
Функция SEARCH (ПОИСК) ищет текстовую строку внутри другой текстовой строки. Она может:
- Найти строку текста внутри другой текстовой строки (без учёта регистра).
- Использовать в поиске символы подстановки.
- Определить стартовую позицию в просматриваемом тексте.
Синтаксис SEARCH (ПОИСК)
Функция SEARCH (ПОИСК) имеет вот такой синтаксис:
- find_text (искомый_текст) – текст, который Вы ищете.
- within_text (текст_для_поиска) – текстовая строка, внутри которой происходит поиск.
- start_num (нач_позиция) – если не указан, то поиск начнётся с первого символа.
Ловушки SEARCH (ПОИСК)
Функция SEARCH (ПОИСК) возвратит позицию первой совпадающей строки, не зависимо от регистра. Если Вам нужен поиск с учётом регистра, Вы можете использовать функцию FIND (НАЙТИ), с которой мы познакомимся далее в рамках марафона 30 функций Excel за 30 дней.
Примеры применения ДЛСТР
Извлекаем слово, содержащее определенный символ.
В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:
Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:
Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.
Теперь разберём пошагово:
Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.
НАЙТИ находит позицию нужного символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций назад и помещает где-то в середине блока из 99 пробелов, который предшествует слову, содержащему указанный символ.
МАКС используется для обработки ситуации, когда нужное значение появляется в начале исходного текста. В этом случае результат ПОИСК() — 50 будет отрицательным числом, а МАКС(1, ПОИСК() — 50) заменяет его на 1.
С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.
Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.
Поиск ячеек
Чтобы отобразились адреса всех ячеек, в которых есть то, что вы ищите, сделайте следующее:
- Если у вас Office 2010, перейдите в Меню — Правка — Найти.
- Откроется окно с полем для ввода. Напишите в нём фразу для поиска.
- В Excel 2007 эта кнопка есть в меню «Главная» на панели «Редактирование». Она находится справа.
- Аналогичного результата во всех версиях можно добиться, если нажать клавиши Ctrl+F.
- В поле напечатайте слово, фразу или цифры, которые надо отыскать.
- Нажмите «Найти всё», чтобы запустить поиск по целому документу. Если кликните «Далее», программа будет по одной выделять клетки, которые находятся ниже курсора-ячейки Excel.
- Подождите, пока процесс закончится. Чем больше документ, тем дольше система будет искать.
- Появится список с результатами: именами и адресами клеток, в которых есть совпадения с заданной фразой, и текстом, который в них написан.
- При клике на каждую строчку будет выделяться соответствующая ячейка.
- Для удобства можете «растянуть» окно. Так в нём будет видно больше строчек.
- Чтобы отсортировать данные, кликайте на названия столбцов над результатами поиска. Если нажмёте на «Лист», строчки выстроятся по алфавиту в зависимости от названия листа, если выберите «Значения» — встанут по значению.
- Эти столбцы также «растягиваются».