Последнее слово
Простая, на первый взгляд, задача с не очевидным решением: извлечь из строки текста последнее слово. Ну или, в общем случае, последний фрагмент, отделенный заданным символом-разделителем (пробелом, запятой и т.д.) Другими словами, необходимо реализовать реверсивный поиск (от конца к началу) в строке заданного символа и извлечь потом все символы справа от него.
Давайте рассмотрим традиционно несколько способов решения на выбор: формулами, макросами и через Power Query.
Способ 1. Формулы
Чтобы проще было понять суть и механику формулы, начнем немного издалека. Сначала увеличим количество пробелов между словами в нашем исходном тексте до, например 20 штук. Сделать это можно при помощи функции замены ПОДСТАВИТЬ (SUBSTITUTE) и функции повтора заданного символа N-раз - ПОВТОР (REPT):
Теперь отрежем от конца получившегося текста 20 символов с помощью функции ПРАВСИМВ (RIGHT):
Уже теплее, да? Осталось убрать лишние пробелы с помощью функции СЖПРОБЕЛЫ (TRIM) и задача будет решена:
В английской версии наша формула будет выглядеть, соответственно:
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";20));20))
Надеюсь, понятно, что в принципе не обязательно вставлять именно 20 пробелов - подойдет любое количество, лишь бы оно было больше, чем длина самого длинного слова в исходном тексте.
И если исходный текст нужно разделить не по пробелу, а по другому символу-разделителю (например, по запятой), то нашу формулу надо будет чуть-чуть подправить:
Способ 2. Макрофункция
Задачу извлечения последнего слова или фрагмента из текста также можно решить с помощью макросов, а именно - написать функцию реверсивного поиска в Visual Basic, которая будет делать то, что нам нужно - искать заданную подстроку в строке в обратном направлении - от конца к началу.
Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), чтобы открыть редактор макросов. Затем добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Function 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)
End Function
Теперь можно сохранить книгу (в формате с поддержкой макросов!) и воспользоваться созданной функцией в следующем синтаксисе:
=LastWord(txt ; delim ; n)
где
- txt - ячейка с исходным текстом
- delim - символ-разделитель (по умолчанию - пробел)
- n - какое по счету слово с конца необходимо извлечь (по умолчанию - первое с конца)
При любых изменениях в исходном тексте в будущем наша макрофункция будет "на лету" пересчитываться, как и любая стандартная функция листа Excel.
Способ 3. Power Query
Power Query - это бесплатная надстройка от Microsoft для импорта данных в Excel из практически любых источников и последующей трансформации загруженных данных в любой вид. Мощь и крутизна этой надстройки настолько велики, что Microsoft встроила все ее возможности в Excel 2016 по умолчанию. Для Excel 2010-2013 Power Query можно бесплатно скачать отсюда.
Наша задача по отделению последнего слова или фрагмента через заданный разделитель с помощью Power Query решается очень легко.
Сначала превратим нашу таблицу с данными в умную с помощью сочтания клавиш Ctrl+T или команды Главная - Форматировать как таблицу (Home - Format as Table):
Затем загрузим созданную "умную таблицу" в Power Query с помощью команды Из таблицы / диапазона (From table/range) на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013):
В открывшемся окне редактора запросов на вкладке Преобразование (Transform) выберем команду Разделить столбец - По разделителю (Split Column - By delimiter) и затем останется задать символ-разделитель и выбрать опцию Самый правый разделитель, чтобы разрубить не все слова, а только последнее:
После нажатия на ОК последнее слово будет отделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув по его заголовку правой кнопкой мыши и выбрав Удалить (Delete). Также можно переименовать оставшийся столбец в шапке таблицы.
Результаты можно выгрузить обратно на лист, используя команду Главная - Закрыть и загрузить - Закрыть и загрузить в ... (Home - Close & Load - Close & Load to...):
И в итоге получаем:
Вот так - дешево и сердито, без формул и макросов, почти не касаясь клавиатуры :)
Если в будущем исходный список изменится, то достаточно будет правой кнопкой мыши или сочетанием клавиш Ctrl+Alt+F5 обновить наш запрос.
Ссылки по теме
- Разделение слипшегося текста по столбцам
- Анализ и разбор текста регулярными выражениями
- Извлечение первых слов из текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Если не требуется это делать формулами иногда использую "найти и заменить" (Ctrl+H) и маску "* ".
В идеале хотелось бы так: =ПСТР(A1;НАЙТИ(" ";A1;-1);ДЛСТР(A1))
Но Excel почему-то не понимает «-1» - как поиск справа…
=ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";ДЛСТР(A1)));ДЛСТР(A1))))>ДЛСТР(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;",";ПОВТОР(" ";ДЛСТР(A1)));ДЛСТР(A1))));СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;",";ПОВТОР(" ";ДЛСТР(A1)));ДЛСТР(A1)));СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";ДЛСТР(A1)));ДЛСТР(A1))))
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";LEN(A1)));LEN(A1)))=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1;",";" ");" ";REPT(" ";LEN(A1)));LEN(A1)))Используя формулу Николая, можно извлекать из Фамилии Имени Отчества - фамилию и инициалы, например,
из ячейки с "Иванов Сергей Петрович" можно получить "Иванов С. П."
вот формула:
=СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";30));30))&" "&ПСТР(A2;ДЛСТР(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";30));30)))+2;1)&". "&ЛЕВСИМВ(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";30));30));1)&"."
А как это просто сделать встроенными - не придумал ещё производитель что-ли?...