Удалить последнее слово в Excel

Иногда при работе с Excel возникает необходимость удалить последнее слово или несколько слов в конце фраз в ячейках.

Для решения задачи можно объединить несколько функций программы, но есть помимо обычных формул и другие способы. Расскажем обо всем по порядку.

Удалить последнее слово из ячейки: формула

В отличие от процедуры удаления первого слова из ячейки, где формула довольно простая в силу того, что функция ПОИСКПОЗ ищет слева направо, формула для удаления последнего слова существенно сложнее.

К сожалению, эквивалента ПОИСКПОЗ, которая искала бы символ в строке не слева направо, а наоборот, справа, нет. Ниже пошаговое описание формулы, которая позволит, тем не менее, сочетанием функций найти его. После этого удалить последнее слово будет уже несложно.

  1. Измеряем длину строки

    В этом поможет функция ДЛСТР.
    =ДЛСТР(А1)

  2. Заменяем пробелы в строке на пустоту

    Здесь поможет функция ПОДСТАВИТЬ.
    =ПОДСТАВИТЬ(A1;» «;»»)

  3. Замеряем длину строки без пробелов

    Формула получается заменой A1 на полное выражение без знака равенства во втором шаге.
    =ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))

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

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

  5. Создаем символ, которого в нашем массиве гарантированно нет

    Если вы точно уверены, можно использовать конкретный символ, например, «\».
    Можно повысить уверенность с помощью функции ЮНИСИМВ — она позволяет вставлять на лист какие угодно символы из UNICODE-таблицы.
    Например, =ЮНИСИМВ(23456)
    Возвращает: 宠
    Полагаю, это китайский или японский.

  6. Заменяем последний пробел на этот символ

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

  7. Находим позицию этого символа

    На этот раз функция ПОИСК замечательно справится. Поскольку символ в строке гарантированно в единственном экземпляре, его можно искать и слева направо.
    =ПОИСК(ЮНИСИМВ(23456);A1;ПОДСТАВИТЬ(A1;» «;ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))

  8. Вычитаем из результата единицу, чтобы узнать позицию символа перед ним

    =ПОИСК(ЮНИСИМВ(23456);A1;ПОДСТАВИТЬ(A1;» «;ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))-1

  9. Извлекаем из исходной ячейки текст до этой позиции

    =ЛЕВСИМВ(A1;ПОИСК(ЮНИСИМВ(23456);A1;ПОДСТАВИТЬ(A1;» «;ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»))))-1)

  10. Во всей формуле меняем A1 на » «&A1

    Чтобы обезопасить себя от ситуаций, когда в ячейке одно слово и чтобы формула в таких случаях его удаляла, а не возвращала ошибку.
    =ЛЕВСИМВ(» «&A1;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(» «&A1;» «;ЮНИСИМВ(23456);ДЛСТР(» «&A1)-ДЛСТР(ПОДСТАВИТЬ(» «&A1;» «;»»))))-1)

  11. «Оборачиваем» результат функцией СЖПРОБЕЛЫ

    Предыдущий шаг добавит обезопасит от ошибок, но добавит лишний пробел слева. Функция удалит его и любые другие лишние пробелы между слов или в конце строки.

Итак, формула удаления последнего слова из ячейки:

=СЖПРОБЕЛЫ(ЛЕВСИМВ(" "&A1;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(" "&A1;" ";ЮНИСИМВ(23456);ДЛСТР(" "&A1)-ДЛСТР(ПОДСТАВИТЬ(" "&A1;" ";""))))-1))

Удаление с помощью регулярного выражения

Регулярные выражения в Excel наделяют специалиста, знакомого с их синтаксисом, практически неограниченными возможностями по поиску, извлечению, замене и удалению текстовых данных без использования монструозных мегаформул, описанных выше.

Пользователям надстройки !SEMTools формулы регулярных выражений доступны сразу же после подключения надстройки к Excel. Вот так изящно будет выглядеть формула удаления последнего слова с помощью функции REGEXREPLACE:

=regexreplace(A1;"\s\S+$";"")

Функциями листа можно пользоваться абсолютно бесплатно, а подробно об этом синтаксисе можно почитать в моей статье:

Регулярные выражения в Excel

Удалить последнее слово/N последних слов во всем столбце

Что делать, если не хочется создавать дополнительный столбец и изменить данные прямо на месте? Здесь вам на помощь придет !SEMTools. Ниже показаны примеры удаления последнего слова или двух/трех/N слов в ячейках, прямо на месте с помощью соответствующего меню «Удалить слова» в группе инструментов «УДАЛИТЬ«:

Удалить последнее слово во всех ячейках с !SEMTools

Удаляем последние 2 слова в конце всех ячеек:

Удалить последние два слова
Удаление последних двух слов в ячейках столбца в 2 клика

Аналогично решается задача для нескольких — можно использовать пункт «Последние N», чтобы указать количество удаляемых слов, считая справа:

Удалить последние слова в ячейке
Удаляем последние 4 слова в ячейках столбца, вводя необходимое количество слов самостоятельно

Заключение

Надеюсь, эта статья помогла вам, и вы с уверенностью сможете удалить последние слова из ячеек. Если хотите делать это, не прибегая к сложным формулам, станьте пользователем надстройки для Excel !SEMTools — она избавит вас от этой рутины и предоставит еще более 500 таких же простых и быстрых функций.

Смотрите похожие разделы:

Удалить первое слово/N первых слов в ячейках Excel

Удалить последние N символов в ячейках столбца

Добавить символ, слово или текст во все ячейки столбца

Удалить из текста список слов