Простая, на первый взгляд, задача с неочевидным решением: извлечь последнее слово из строки текста. Ну или в общем случае последний фрагмент, разделенный заданным символом-разделителем (пробел, запятая и т.п.). Другими словами, необходимо реализовать обратный поиск (от конца к началу) в строке данный символ, а затем извлеките все символы справа от него.
Давайте рассмотрим традиционно несколько способов выбора: формулы, макросы и через Power Query.
Способ 1. Формулы
Чтобы было проще понять суть и механику формулы, начнем немного издалека. Для начала увеличим количество пробелов между словами в нашем исходном тексте, например, до 20 штук. Вы можете сделать это с помощью функции замены. ЗАМЕНА (ЗАМЕНЯТЬ) и функция повторения заданного символа N-раз – ПОВТОРЕНИЕ (РЕПТ):
Теперь отрезаем 20 символов от конца полученного текста с помощью функции ПРАВО (ВЕРНО):
Становится теплее, да? Осталось убрать лишние пробелы с помощью функции TRIM (ОТДЕЛКА) и проблема будет решена:
В английской версии наша формула будет выглядеть так:
=TRIM(ПРАВО(ЗАСТАВИТЬ(A1;» «;REPT(» «;20));20))
Надеюсь, понятно, что вставлять ровно 20 пробелов в принципе не обязательно — подойдет любое число, главное, чтобы оно было больше длины самого длинного слова в исходном тексте.
А если исходный текст нужно разделить не пробелом, а другим символом-разделителем (например, запятой), то нашу формулу нужно будет немного подкорректировать:
Способ 2. Макрос-функция
Задачу извлечения последнего слова или фрагмента из текста можно решить и с помощью макросов, а именно написать на Visual Basic функцию обратного поиска, которая будет делать то, что нам нужно – искать заданную подстроку в строке в обратном направлении – от конец к началу.
Нажмите сочетание клавиш другой+F11 или кнопка Визуальный Бейсик таб разработчик (Разработчик)чтобы открыть редактор макросов. Затем добавьте новый модуль через меню Вставка – Модуль и скопируйте туда следующий код:
Функция LastWord(txt As String, необязательный разделитель As String = " ", необязательный n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) Конечная функция
Теперь вы можете сохранить книгу (в формате с поддержкой макросов!) и использовать созданную функцию в следующем синтаксисе:
=LastWord(txt; разделитель; n)
в котором
- текст – ячейка с исходным текстом
- DELIM — символ-разделитель (по умолчанию — пробел)
- n – какое слово следует извлечь с конца (по умолчанию – первое с конца)
При любых изменениях исходного текста в дальнейшем наша макрофункция будет пересчитываться на лету, как и любая стандартная функция листа Excel.
Способ 3. Power Query
Power Query — бесплатное дополнение от Microsoft для импорта данных в Excel практически из любого источника и последующего преобразования загруженных данных в любую форму. Мощь и крутость этой надстройки настолько велики, что Microsoft по умолчанию встроила все ее функции в Excel 2016. Для Excel 2010-2013 Power Query можно бесплатно скачать здесь.
Наша задача по разделению последнего слова или фрагмента через заданный разделитель с помощью Power Query решается очень легко.
Во-первых, давайте превратим нашу таблицу данных в интеллектуальную таблицу, используя сочетания клавиш. Ctrl+T или команды Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу):
Затем загружаем созданную «умную таблицу» в Power Query с помощью команды Из таблицы/диапазона (Из таблицы/диапазона) таб Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013):
В открывшемся окне редактора запросов на вкладке трансформация (Преобразование) выбрать команду Разделить столбец – по разделителю (Разделить столбец — по разделителю) а затем остается задать символ-разделитель и выбрать опцию Крайний правый разделительвырезать не все слова, а только последнее:
После нажатия на OK последнее слово будет выделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув его заголовок правой кнопкой мыши и выбрав Удалить (Удалить). Вы также можете переименовать оставшийся столбец в заголовке таблицы.
Результаты можно загрузить обратно на лист с помощью команды Домой — Закрыть и загрузить — Закрыть и загрузить в… (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…):
И в результате мы получаем:
Вот так – дешево и сердито, без формул и макросов, почти не трогая клавиатуру 🙂
Если в будущем исходный список изменится, достаточно будет щелкнуть правой кнопкой мыши или использовать сочетание клавиш. Ctrl+другой+F5 обновить наш запрос.
- Разделение прикрепленного текста на столбцы
- Парсинг и разбор текста с помощью регулярных выражений
- Извлечение первых слов из текста с помощью функции ПОДСТАВИТЬ