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