
Иногда при работе с Excel возникает необходимость удалить последнее слово или несколько слов в конце фраз в ячейках.
Для решения задачи можно объединить несколько функций программы, чтобы осуществить поиск справа налево в ячейке, но есть помимо обычных формул и другие способы. Расскажу обо всем по порядку.
Удалить последнее слово из ячейки: формула
В отличие от процедуры удаления первого слова из ячейки, где формула довольно простая в силу того, что функция ПОИСК ищет слева направо, формула для удаления последнего слова существенно сложнее.
К сожалению, эквивалента ПОИСК, которая искала бы символ в строке не слева направо, а наоборот, справа, нет. Ниже пошаговое описание формулы, которая позволит, тем не менее, сочетанием функций найти его. После этого удалить последнее слово будет уже несложно.
1. Измеряем длину строки.
В этом поможет функция ДЛСТР.
=ДЛСТР(A1)
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 слов в ячейках, прямо на месте с помощью соответствующего меню “Удалить слова” в группе инструментов «УДАЛИТЬ»:

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

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

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