Последнее слово

Простая, на первый взгляд, задача с не очевидным решением: извлечь из строки текста последнее слово. Ну или, в общем случае, последний фрагмент, отделенный заданным символом-разделителем (пробелом, запятой и т.д.) Другими словами, необходимо реализовать реверсивный поиск (от конца к началу) в строке заданного символа и извлечь потом все символы справа от него.

Давайте рассмотрим традиционно несколько способов решения на выбор: формулами, макросами и через Power Query.

Способ 1. Формулы

Чтобы проще было понять суть и механику формулы, начнем немного издалека. Сначала увеличим количество пробелов между словами в нашем исходном тексте до, например 20 штук. Сделать это можно при помощи функции замены ПОДСТАВИТЬ (SUBSTITUTE) и функции повтора заданного символа N-раз - ПОВТОР (REPT):

Добавляем пробелы между словами

Теперь отрежем от конца получившегося текста 20 символов с помощью функции ПРАВСИМВ (RIGHT):

Берем последние 20 знаков

Уже теплее, да? Осталось убрать лишние пробелы с помощью функции СЖПРОБЕЛЫ (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 - какое по счету слово с конца необходимо извлечь (по умолчанию - первое с конца)

Применение функции LastWord

При любых изменениях в исходном тексте в будущем наша макрофункция будет "на лету" пересчитываться, как и любая стандартная функция листа 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):

Загружаем таблицу в Power Query

В открывшемся окне редактора запросов на вкладке Преобразование (Transform) выберем команду Разделить столбец - По разделителю (Split Column - By delimiter) и затем останется задать символ-разделитель и выбрать опцию Самый правый разделитель, чтобы разрубить не все слова, а только последнее:

Делим по правому разделителю

После нажатия на ОК последнее слово будет отделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув по его заголовку правой кнопкой мыши и выбрав Удалить (Delete). Также можно переименовать оставшийся столбец в шапке таблицы.

Результаты можно выгрузить обратно на лист, используя команду Главная - Закрыть и загрузить - Закрыть и загрузить в ... (Home - Close & Load - Close & Load to...):

Выгружаем результаты на лист

И в итоге получаем:

Последнее слово из текста

Вот так - дешево и сердито, без формул и макросов, почти не касаясь клавиатуры :)

Если в будущем исходный список изменится, то достаточно будет правой кнопкой мыши или сочетанием клавиш Ctrl+Alt+F5 обновить наш запрос.

Ссылки по теме




01.05.2018 11:07:00
Спасибо.
Если не требуется это делать формулами иногда использую "найти и заменить" (Ctrl+H) и маску "* ".
03.05.2018 11:17:29
:):):):) самый крутой способ
03.05.2018 10:41:53
Николай, спасибо! Очень креативное решение на формулах…

В идеале хотелось бы так: =ПСТР(A1;НАЙТИ(" ";A1;-1);ДЛСТР(A1))

Но Excel почему-то не понимает «-1» - как поиск справа…
08.05.2018 13:37:39
Спасибо Николай!
11.05.2018 16:04:22
Объединил формулы, где отбор по 2-м критериям (пробел, или запятая)
=ЕСЛИ(ДЛСТР(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";ДЛСТР(A1)));ДЛСТР(A1))))>ДЛСТР(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;",";ПОВТОР(" ";ДЛСТР(A1)));ДЛСТР(A1))));СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;",";ПОВТОР(" ";ДЛСТР(A1)));ДЛСТР(A1)));СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A1;" ";ПОВТОР(" ";ДЛСТР(A1)));ДЛСТР(A1))))
19.05.2018 21:35:07
20 что указывается в формульном примере проще всего заменить на ДЛСТР(A1) и забыть
 =TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";LEN(A1)));LEN(A1)))
ну а если разделителей несколько, то проще сделать двойную замену, например сперва "," на " " , а потом " " на ПОВТОР("
 =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1;",";" ");" ";REPT(" ";LEN(A1)));LEN(A1)))
10.07.2018 20:21:21
Добавлю. В PQ можно получить данные не только из умной таблицы, но и из диапазона данных. Достаточно его таковым обозвать..
22.08.2018 02:18:44
Всем привет!
Используя формулу Николая, можно извлекать из Фамилии Имени Отчества - фамилию и инициалы, например,
из ячейки с "Иванов Сергей Петрович" можно получить "Иванов С. П."
вот формула:
=СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";30));30))&" "&ПСТР(A2;ДЛСТР(СЖПРОБЕЛЫ(ЛЕВСИМВ(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";30));30)))+2;1)&". "&ЛЕВСИМВ(СЖПРОБЕЛЫ(ПРАВСИМВ(ПОДСТАВИТЬ(A2;" ";ПОВТОР(" ";30));30));1)&"."
Наверх