Как получить последнее слово в excel

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

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

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

Если строка в ячейке содержит несколько слов, например, «Василий Иванович Петров», то можно создать формулу для вывода последнего слова.

Пусть текстовая строка

Василий Иванович Петров

находится в ячейке

A

1

. Выведем последнее слово (см.

файл примера

):

=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«^^»; ПОДСТАВИТЬ(A1;» «;»^^»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))))

В формуле предполагается, что слова разделены пробелами (код символа = 32). Перед применением формулы убедитесь, что в строке нет лишних пробелов. Это можно сделать функцией

СЖПРОБЕЛЫ()

.

В статье

Выбор из текстовой строки n-го слова

приведено решение подобной задачи в общем случае.

Вообще,

хранить несколько текстовых значений в одной ячейке неправильно

, отсюда и возникают такого рода задачи, имеющие громоздкие решения.

Как альтернативу можно посоветовать воспользоваться инструментом

Текст-По-Столбцам

(

), позволяющему, разделить текстовую строку на несколько строк.

Если необходимо из текстовой строки вывести первое слово, то читайте статью

Выбор из строки первого слова

.

Формулы в этой статье полезны для извлечения слов из текста, содержащегося в ячейке. Например, вы можете создать формулу для извлечения первого слова в предложении.

Извлечение первого слова из строки

Чтобы извлечь первое слово из строки, формула должна найти позицию первого символа пробела, а затем использовать эту информацию в качестве аргумента для функции ЛЕВСИМВ. Следующая формула делает это: =ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1).

Эта формула возвращает весь текст до первого пробела в ячейке A1. Однако у нее есть небольшой недостаток: она возвращает ошибку, если текст в ячейке А1 не содержит пробелов, потому что состоит из одного слова. Несколько более сложная формула решает проблему с помощью новой функции ЕСЛИОШИБКА, отображая все содержимое ячейки, если произошла ошибка:
=ЕСЛИОШИБКА(ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1);A1).

Если вам нужно, чтобы формула была совместима с более ранними версиями Excel, вы не можете использовать ЕСЛИОШИБКА. В таком случае придется обойтись функцией ЕСЛИ и функцией ЕОШ для проверки на ошибку:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1))

Извлечение последнего слова строки

Извлечение последнего слова строки — более сложная задача, поскольку функция НАЙТИ работает только слева направо. Таким образом, проблема состоит в поиске последнего символа пробела. Следующая формула, однако, решает эту проблему. Она возвращает последнее слово строки (весь текст, следующий за последним символом пробела):
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"";"")))))

Но у этой формулы есть такой же недостаток, как и у первой формулы из предыдущего раздела: она вернет ошибку, если строка не содержит по крайней мере один пробел. Решение заключается в использовании функции ЕСЛИОШИБКА и возврате всего содержимого ячейки А1, если возникает ошибка:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;" ";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")))));A1)

Следующая формула совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));A1;ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ("*";ПОДСТАВИТЬ(A1;"";"*";ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))))

Извлечение всего, кроме первого слова строки

Следующая формула возвращает содержимое ячейки А1, за исключением первого слова:
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ":A1;1)).
Если ячейка А1 содержит текст 2008 Operating Budget, то формула вернет Operating Budget.

Формула возвращает ошибку, если ячейка содержит только одно слово. Следующая версия формулы использует функцию ЕСЛИОШИБКА, чтобы можно было избежать ошибки; формула возвращает пустую строку, если ячейка не содержит более одного слова:
=ЕСЛИОШИБКА(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1));"")

А эта версия совместима со всеми версиями Excel:
=ЕСЛИ(ЕОШ(НАЙТИ(" ";A1));"";ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;1)))

Скачать пример рабочей книги

Загрузите образец книги

В этом руководстве будет показано, как извлечь последнее слово из ячейки в Excel и Google Таблицах.

Извлечь последнее слово

Если бы мы хотели извлечь первое слово ячейки, мы могли бы просто использовать функцию FIND, чтобы найти первый пробел, и функцию LEFT, чтобы вывести слово перед пробелом.

К сожалению, в Excel нет функции обратного поиска, поэтому вместо этого нам нужно будет разработать другое решение:

1. Мы будем использовать функцию SUBSTITUTE, чтобы заменить пробелы между словами большим количеством (n) пробелов.
2. Мы будем использовать функцию RIGHT, чтобы вычислить n правильных пробелов. (Это будет включать наше слово, а также ряд дополнительных пробелов)
3. Мы будем использовать функцию TRIM, чтобы вырезать лишние пробелы, оставив только последнее слово.

Вот формула, которую мы будем использовать:

= ОБРЕЗАТЬ (ПРАВО (ПОДСТАВИТЬ (B3; ""; ПОВТОР (""; 10)); 10))

Обратите внимание, что для этого примера мы выбрали n = 10. Это слишком маленькое число: ячейка C6 не содержит полного последнего слова, потому что слово состоит из 11 символов.

Мы выбрали небольшое число для демонстрационных целей, но мы рекомендуем вам использовать гораздо большее число (например, 99) или использовать универсальную формулу, приведенную в конце этого руководства.

Теперь давайте рассмотрим формулу:

Функция ПОВТОР

Функция REPT повторяет символ (n) раз. Эта формула создаст 10 пробелов:

= ПОВТОР (""; 10)

Чтобы показать, с кем будет работать эта функция, мы заменили пробелы дефисами (-):

ЗАМЕНА Функция

Функция ЗАМЕНА находит все пробелы в текстовой строке и заменяет пробелы на 10 пробелов.

= ПОДСТАВИТЬ (B3; ""; C3)

Функция ВПРАВО

Функция ВПРАВО извлекает из ячейки последние 10 символов:

= ВПРАВО (D3,10)

Функция обрезки

Функция TRIM удаляет все начальные и конечные пробелы из текста и возвращает только последнее слово:

= ОБРЕЗАТЬ (E3)

Универсальная формула

Вместо определения числа (n) вы можете использовать функцию LEN для вычисления n как количества символов в ячейке. Эта формула будет работать независимо от того, насколько велико последнее слово.

= ОБРЕЗАТЬ (ВПРАВО (ПОДСТАВИТЬ (B3; ""; ПОВТОР (""; LEN (B3))); LEN (B3)))

Формула для извлечения последнего слова из текста работает в Google Таблицах точно так же, как и в Excel:

Вы поможете развитию сайта, поделившись страницей с друзьями

In this article, we explain how to extract the last word from a text in a cell using the Excel function.  Extracting words from a text is an important task in text processing.  

Eg.  Suppose we have a data file with a field called “Product_Category”, which was combined both product name and their respective category name with space as below.  Assume that the last word from the “Product_Category” field is a category.  We have to extract categories from the given data in column B for further analysis.

Sample Data:

We use the below list of 4 Excel user-defined functions to extract the last word.

1. REPT()

Syntax:

REPT(text, number)

Where,

• text – character to repeat
• number – number of times to repeat the character

Eg: REPT(“*“,10) – returns **********

2. SUBSTITUTE()

Syntax:

SUBSTITUTE( text, old_text, new_text, [instance_number] )

Where,

• text – the original text 
• old_text – text need to replace
• new_text –text replace with old text
• [instance_number] – Optional. The number indicates the instance number of old text to replace

Eg: SUBSTITUTE(“Filo Mix”,” “,REPT(“*”,10)) – returns Filo**********Mix

3. RIGHT()

Syntax:

RIGHT( text, [number_of_characters] )

Where,

• text – original text 
• [number_of_characters] – optional. Number characters extract from the right.

Eg: RIGHT(“Filo**********Mix”) – returns *******Mix

4. TRIM()

Syntax:

TRIM(text)

Where,

• text – Removes leading and trailing spaces

Eg: Eg: Trim(“Mix”) – returns Mix 

Implementation:

Follow the below steps to  Extract the last word from a Cell in Excel:

Step 1: Write header “Category” in cell B1.

Step 2: Write the below formula to cells “B2”.  In the given data category name is not more than 10 characters.  So we used 10 in both REPT() and SUBSTITUTE().  You can use any number greater than the maximum of the last word.

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",10)),10))

Step 3: Drag formula B2 to B14 to fill the same formula to all other cells