Поиск с конца строки, справа налево в Excel
Как найти первый символ с конца строки?
Поиск позиции последнего вхождения значения внутри ячейки Excel — довольно сложная задача.
Тем не менее, иногда ее необходимо решить, например, чтобы удалить или заменить это вхождение.
Все дело в том, что функции поиска позиции ПОИСК и НАЙТИ ищут только с начала ячейки, и у них нет параметра переключения на поиск с конца.
Процедура Найти и Заменить также не подойдет. Она ищет не с конца строки, а просто находит (и заменяет) все вхождения.
Ниже я покажу пару способов, как осуществить поиск с конца строки.
Обычная составная формула
Формула, похожая на ту, что ниже, рассматривается подробно в статье о том, как удалить последнее слово в ячейке Excel. Поиск пробела с конца строки как раз является необходимым в этом случае.
- Фрагмент, обозначенный красным цветом, вычисляет количество пробелов в ячейке. Подробнее можно почитать в описании функции ПОДСТАВИТЬ.
- Это количество является аргументом еще одной подстановки, где заменяется лишь последний пробел (выделено жирным)
- Вместо пробела в его последнюю позицию вставляется достаточно редкий символ. В данном случае это иероглиф, который создается функцией СИМВОЛ. Но можно и прописать символ вручную. В формуле ниже это обратная косая черта:
-
находит позицию этого символа. Поскольку он встречается в строке всего один раз и стоит на месте последнего пробела, это и позволяет нам сказать, что поиск произведен справа налево.
Если вам необходимо найти с конца строки какой-то другой символ или текстовый фрагмент, замените пробелы в этой формуле на него.
Примеры использования составной формулы для поиска с конца строки
Формулы массива для поиска символа с конца строки
Поиск слева направо с помощью функции ПОДСТАВИТЬ, описанный выше, имеет пару недостатков.
Первый — регистрозависимость этой функции, но это легко учесть, обернув нужные фрагменты функциями изменения регистра.
А второй уже серьезнее — формула не очень универсальна, т.к. использует замену на символ, который может оказаться в строке, и тогда она выдаст неверное значение.
От обоих проблем избавят формулы массива. Они обе создают массивы значений, внутри которых функция выбирает последнее.
С помощью МАКС
Формула ниже находит позицию символа «а» в любом регистре.
-
измеряет длину ячейки в символах
- ДВССЫЛ создает из текстового представления длины ссылку на диапазон строк с 1 по строку, равную этой длине
- Функция СТРОКА возвращает массив чисел, соответствующих этим длинам, соответственно, , обрабатывая этот массив, возвращает для каждого числа символ, стоящий на этой позиции в строке
- Текстовое сравнение с символом «а» возвращает булевый массив (значения ИСТИНА или ЛОЖЬ)
- Этот массив умножается на повторно созданный массив чисел (пункты 1:3). ЛОЖЬ эквивалентна нулю, а ИСТИНА — единице, поэтому для всех символов, не равных «а», в результирующем числовом массиве будут нули, а для равных — их позиции
- Функция МАКС возвращает наибольшее число в этом массиве.
С помощью ПОИСКПОЗ
Чуть более хитрая механика делает формулу короче, вместе с тем существенно быстрее, а задействует функцию ПОИСКПОЗ:
- Как и в предыдущем варианте, с помощью тех же функций ДЛСТР, СТРОКА, ДВССЫЛ, ПСТР и текстового сравнения создается булевый массив;
- Но на этом этапе единица делится на него. ЛОЖЬ эквивалентна нулю и выдается ошибка деления на ноль. ИСТИНА возвращает единицу.
- Функция ПОИСКПОЗ с последним параметром «1» при поиске 2 (на месте 2 может быть любое число больше 1) возвращает позицию последнего наибольшего числа, меньшее, чем 2. Т.е. последней единицы, которой и соответствует последний найденный в строке символ.
Часто сталкиваетесь с этой или похожими задачами при работе в Excel?
Сотни инструментов надстройки для Excel !SEMTools помогут вам упростить их решение и сэкономят ваше время!
Значок диаметр в excel
- * (звездочка); Обозначает любое произвольное количество символов. Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
- ? (вопросительный знак); Обозначает один произвольный символ. К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.
- ~ (тильда) с последующими знаками *, ? или ~. Обозначает конкретный символ *, ? или ~. Например, поиск по фразе «хор*» найдет все фразы начинающиеся на «хор» («хоровод», «хорошо» и т.д.). Поэтому для точного поиска «хор*» нужно использовать символ «~» и искать по фразе «хор~*». Наличие «~» гарантирует, что Excel прочитает следующий символ как текст, а не как подстановочный знак.
Аналогичная ситуация и при замене или удалении вопросительного знака и тильды.
Производя замену «~?» (для тильды — «~~») мы также без проблем сможем заменить или удалить спецсимвол.
Функция ЛЕВСИМВ в EXCEL с примерами формул и пояснениями.
Важно: В Word и Outlook точки соединения можно использовать только в том случае, когда линии и объекты, к которым они прикрепляются, помещены на полотно. Чтобы вставить полотно, на вкладке Вставка нажмите кнопку Фигуры и щелкните Новое полотно в нижней части меню.
Юникод
- Текст (обязательно) — это текст, из которого вы хотите извлечь подстроку. Обычно предоставляется как ссылка на ячейку, в которой он записан.
- Второй аргумент (необязательно) — количество знаков для извлечения, начиная слева.
- Если параметр опущен, то по умолчанию подразумевается 1, то есть возвращается 1 знак.
- Если введенное значение больше общей длины ячейки, формула вернет всё ее содержимое.
Итак, как заставить ЛЕВСИМВ выводить числовое значение, а не текстовую строку, состоящую из цифр? Просто заключив его в функцию ЗНАЧЕН (VALUE), которая предназначена для преобразования текста, состоящего из цифр, в число.
