Excel как искать с конца

Поиск с конца строки в Excel

Описание функции

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

Вот этот набор функций, не очень то очевидно, согласитесь:

=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«Заданный текст»;A1;1)-ДЛСТР(«Заданный текст»))

Для удобства в надстройку VBA-Excel добавлена функция КОНЕЦСТРОКИ. Она выполняет поиск заданного текста с конца строки и возвращает текст от искомой позиции до конца строки. И интерфейс у нее намного понятнее:

Функция =КОНЕЦСТРОКИ(ТЕКСТ; НАЙТИ; [ Старт ]; [ Регистр ]) имеет 4 аргумента:

• ТЕКСТ — Исходный текст, в котором необходимо найти начало строки.
• НАЙТИ — Подстрока, до которой будет возвращаться начало строки
• [ Старт ] — Необязательный параметр. Позиция внутри исходного текста, с которой начинается поиск подстроки. По умолчанию параметр равен 1.
• [ Регистр ] — Необязательный аргумент, указывающий необходимость учета регистра. По умолчанию равен 1 — регистр учитывается. Укажите значение 0 если не хотите учитывать регистр при сравнении.

Пример 1

Найти текст до слова СТОП. Обратите внимание не важно в какой части текста находится заданное слово.

Пример 2

Чтобы вернуть текст без первого слова укажите пробел в параметре НАЙТИ.

Пример 3

Можно начинать поиск с заданной позиции (аргумент Старт ). Может понадобиться в случае если стоп-слово встречается не один раз.

Пример 4

В предыдущих примерах регистр искомого текста учитывался. Для того чтобы заглавные и строчные буквы НЕ учитывались при поиске, установите параметр Регистр = 0.

Источник

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.

Описание

Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:

Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».

Можно также находить слова в других словах. Например, функция

возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.

Эти функции могут быть доступны не на всех языках.

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

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ПОИСК и ПОИСКБ описаны ниже.

Искомый_текст Обязательный. Текст, который требуется найти.

Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.

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

Замечание

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

В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак ( ?) и звездочку ( *). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (

Если значение find_text не найдено, #VALUE! возвращается значение ошибки.

Если аргумент начальная_позиция опущен, то он полагается равным 1.

Если start_num больше нуля или больше, чем длина аргумента within_text, #VALUE! возвращается значение ошибки.

Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.

Примеры

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

Источник

Как получить слово после последнего пробела

Получить слово до первого пробела достаточно просто:
=ПСТР( A1 ;1;НАЙТИ(» «; A1 )-1)
=MID(A1,1,FIND(» «,A1)-1)
Но куда чаще сложности возникают с получением слова(символа), находящегося на определенной позиции между пробелом. Я беру в качестве примера пробел, но на самом деле это может быть абсолютно любой символ. Например, для получения второго слова(т.е. между первым и вторым пробелом), можно составить такую формулу:
=ПСТР( A1 ;НАЙТИ(» «; A1 )+1;НАЙТИ(» «; A1 ;НАЙТИ(» «; A1 )+1)-НАЙТИ(» «; A1 )-1)
=MID(A1,FIND(» «,A1)+1,FIND(» «,A1,FIND(» «,A1)+1)-FIND(» «,A1)-1)
На мой взгляд, выглядит несколько закручено, хотя все не так уж сложно:

• НАЙТИ(» «; A1 )+1 — ищем позицию первого пробела в ячейке A1
• НАЙТИ(» «;A1;НАЙТИ(» «; A1 )+1) — ищем позицию второго пробела и затем из этой позиции вычитаем позицию первого пробела( -НАЙТИ(» «; A1 ) )

Но есть проблема — если второго пробела нет, то формула выдаст ошибку #ЗНАЧ! (#VALUE!) . Тогда придется еще и проверку на ошибку делать, что явно не добавит формуле элегантности. А если надо не второе слово, а третье, пятое? Поэтому я предпочитаю использовать такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));1;999*2);999);» «;»»)
=SUBSTITUTE(RIGHT(MID(» «&SUBSTITUTE(A1,» «,REPT(» «,999)),1,999*2),999),» «,»»)
На первый взгляд куда кошмарнее, чем первая. Но у неё есть ряд преимуществ:
— она не нуждается в проверке на отсутствие пробелов
— изменением одного числа можно получить не второе, а 3-е, 4-е и т.д. слово.

Разберем основные моменты использования этой формулы. Во-первых: формула вытаскивает второе слово от начала строки. Во-вторых: чтобы получить первое слово от начала строки, нужно в блоке 999*2 заменить 2 на 1:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));1;999*1);999);» «;»»)
=SUBSTITUTE(RIGHT(MID(» «&SUBSTITUTE(A1,» «,REPT(» «,999)),1,999*1),999),» «,»»)
Чтобы получить 5-е слово — меняем на 5:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));1;999*5);999);» «;»»)
=SUBSTITUTE(RIGHT(MID(» «&SUBSTITUTE(A1,» «,REPT(» «,999)),1,999*5),999),» «,»»)
Т.е. число — это позиция слова(или слов) между пробелами. А что будет, если мы укажем число больше, чем есть пробелов в строке?

А это как раз ТО, К ЧЕМУ ШЛИ — СЛОВО ПОСЛЕ ПОСЛЕДНЕГО ПРОБЕЛА
Если вдруг число будет больше, чем есть пробелов в строке — то мы получим слово после последнего пробела (т.е. первое слово с конца строки). Это значит, что если указать, например, *999 — в большинстве случаев получим как раз последнее слово.

Как это работает:
для примера возьмем текст «мама мыла раму» и формулу по получению второго слова от начала:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));1;999*2);999);» «;»»)
=SUBSTITUTE(RIGHT(MID(» «&SUBSTITUTE(A1,» «,REPT(» «,999)),1,999*2),999),» «,»»)

• Сначала при помощи функции ПОДСТАВИТЬ (SUBSTITUTE) мы заменяем все пробелы в тексте на 999 пробелов(999 получаем при помощи функции ПОВТОР (REPT) . Число может быть и меньше 999, но не должно быть меньше длины исходной строки. В итоге мы получим очень длинную строку, в которой каждое слово будет отделено от другого 999 пробелами. Что-то вроде такого(пробелов я поставил меньше, конечно):
  «мама____________________________мыла____________________________раму»
• Далее при помощи функции ПСТР (MID) мы берем все слова от начала строки, до символа на позиции 999*2. Т.е. из текста выше мы получим слова «мама» и «мыла» и по 999 символов после каждого:
  «мама____________________________мыла____________________________»
• Затем при помощи функции ПРАВСИМВ (RIGHT) получаем 999 символов справа от строки. Т.е. только наше слово и куча пробелов после него
  «мыла____________________________»
• И напоследок та же функция ПОДСТАВИТЬ (SUBSTITUTE) убирает более не нужные нам пробелы, заменяя их все на пустую строку — «» .

Вроде бы достигли того, что нам нужно было. Но вдруг необходимо получить второе слово с конца строки? А если у нас этих слов десятки? Можно использовать некую модификацию приведенной выше формулы, но которая как раз возвращает слово с конца строки:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(» «&ПОДСТАВИТЬ( A1 ;» «;ПОВТОР(» «;999));999*1);1;999);» «;»»)
=SUBSTITUTE(MID(RIGHT(» «&SUBSTITUTE(A1,» «,REPT(» «,999)),999*1),1,999),» «,»»)
Принцип тот же: если в блоке 999*1 заменить 1 на 5, то получим 5-е слово с конца строки.
Если необходимо выдергивать слова именно по пробелам, то лучше дополнить еще одной функцией — СЖПРОБЕЛЫ (TRIM) , чтобы отсечь лишние пробелы в начале и в конце строки и оставить только одиночные пробелы между словами:

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

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

Тогда для получения второй строки( ТЦ Таганка и ТЦ Опус ) можно применить такую формулу:
=ПОДСТАВИТЬ(ПРАВСИМВ(ПСТР(СИМВОЛ(10)&ПОДСТАВИТЬ( C2 ;СИМВОЛ(10);ПОВТОР(СИМВОЛ(10);999));1;999*2);999);СИМВОЛ(10);»»)
СИМВОЛ(10) здесь означает перенос строки. Обычно эти переносы делаются с клавиатуры. Входим в режим редактирования ячейки, ставим курсор в нужное место строки и нажимаем Alt+Enter.
я для получения месяцев( Август 2015 г. и Сентябрь 2015 г. ) — такую:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(«/»&ПОДСТАВИТЬ( C2 ;»/»;ПОВТОР(«/»;999));999*1);1;999);»/»;»»)
В этой формуле в качестве разделителя используется слеш «/».

Разбор основных параметров формулы для применения в своих файлах
По сути, после нескольких примеров, основной принцип должен быть понятен. Но все же на примере последней формулы напомню про основные моменты и что надо сделать, чтобы применить практически к любой ситуации по извлечению слов:
=ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ(«/»&ПОДСТАВИТЬ( C2 ;»/»;ПОВТОР(«/»;999));999*1);1;999);»/»;»»)

1. C2 — ячейка с текстом, последнее слово из которого надо извлечь.
2. «/» — разделитель слов. Если для разделения слов используется не слеш, а запятая или точка — то во всей формуле, где встречается этот символ надо заменить его на нужный.
   Если лень прописывать этот символ внутри формулы несколько раз, его можно записать в отдельную ячейку(скажем, G1 ) и в формуле указать ссылку уже на эту ячейку:
   =ПОДСТАВИТЬ(ПСТР(ПРАВСИМВ( G1 &ПОДСТАВИТЬ( C2 ; G1 ;ПОВТОР( G1 ;999));999*1);1;999); G1 ;»»)
   Теперь для изменения символа надо будет изменить его один раз в ячейке G1 и формула «вытащит» нужное слово/строку, опираясь именно на этот символ.
3. *1 — число 1 — позиция слова с конца строки, которое необходимо «достать». 1 — первое с конца(т.е. последнее), 2 — предпоследнее и т.д.

Зная эти основные моменты достаточно будет в своем файле просто скопировать формулу выше и подставить в неё ссылку на нужную ячейку и указать требуемый разделитель.

Слово после последнего пробела.xls (29,5 KiB, 3 760 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Источник

Поиск с конца строки, справа налево в Excel

Как найти первый символ в конце строки?

Найти позицию последнего вхождения значения в ячейке Excel — довольно сложная задача.

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

Так в чем проблема?

Дело в том, что функции поиска местоположения SEARCH и FIND выполняют поиск только с начала ячейки и не имеют возможности перейти к поиску с конца.

Процедура «Найти и заменить» также не будет работать. Он не ищет с конца строки, он просто находит (и заменяет) все вхождения.

Ниже я покажу вам несколько способов поиска с конца строки.

Обычная составная формула

Формула, аналогичная приведенной ниже, подробно описана в статье Как удалить последнее слово в ячейке Excel. В этом случае необходимо искать пробел с конца строки.

Следующая формула ищет пробел от конца ячейки A1:

= ПОИСК (UNISIMV (23456); ПОДСТАВИТЬ (A1; «»; UNISIMV (23456); DLSTR (A1) -LSTR (ПОДСТАВИТЬ (A1;» «;»»))))

Как это работает:

• В разделе, выделенном красным, рассчитывается количество пробелов в ячейке. Более подробную информацию можно найти в описании функции ЗАМЕНИТЬ.
• Этот номер является аргументом в пользу другой замены, где заменяется только последний пробел (выделенный жирным шрифтом)
• Вместо пробела на его последней позиции вставляется довольно редкий символ. В данном случае это иероглиф, созданный функцией SYMBOL. Но вы также можете зарегистрировать символ вручную. В приведенной ниже формуле это обратная косая черта:

= ПОИСК («»; ПОДСТАВИТЬ (A1; «»; «»; DLSTR (A1) -LSTR (ПОДСТАВИТЬ (A1;» «;»»))))

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

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

Примеры использования составной формулы для поиска с конца строки

Формулы массива для поиска символа с конца строки

Поиск слева направо с помощью описанной выше функции REPLACE имеет несколько недостатков.

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

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

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

С помощью МАКС

В любом случае следующая формула находит положение символа «а.

Механика его работы пошаговая:

1. Функция DLSTR измеряет длину ячейки в символах
2. INDIRECT создает из текстового представления длины ссылку на диапазон строк от 1 до строки, равной этой длине
3. Функция СТРОКА возвращает массив чисел, соответствующих этим длинам, соответственно,
4. Функция MID, обрабатывая этот массив, возвращает символ в этой позиции в строке для каждого числа
5. Текстовое сравнение с символом «a» возвращает логический массив (значения TRUE или FALSE)
6. Эта матрица умножается на воссозданную матрицу чисел (точки 1: 3). FALSE эквивалентно нулю, а TRUE эквивалентно единице, поэтому для всех символов, которые не равны «a», результирующий числовой массив будет содержать нули, а для равных — их позиции
7. Функция MAX возвращает наибольшее число в этом массиве.

С помощью ПОИСКПОЗ

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

<= SEARCH (2; 1 / (MID (A1; LINE (INDIRECT («1:» & DLSTR (A1)))); 1) = «a»); 1)>ВАЖНО: это формула массива! Вы входите без фигурных скобок. Но не клавишей Enter, а комбинацией: Ctrl + Shift + Enter После этого фигурные скобки появятся сами собой. Если вы введете формулу обычным способом, она не сработает.

1. Как и в предыдущей версии, с использованием тех же функций DLSTR, STRING, INDIRECT, MID и сравнения текста создается логический массив;
2. Но на данном этапе на него делится единство. FALSE эквивалентно нулю, и выводится ошибка деления на ноль. ИСТИНА возвращает единицу.
3. Функция ПОИСКПОЗ с последним параметром «1» при поиске 2 (на месте 2 может быть любое число больше 1) возвращает позицию последнего наибольшего числа меньше 2) То есть последнюю единицу, которая соответствует последней в строке найден символ.

Источник