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

Иногда при работе с 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.

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

• Удалить первое слово/N первых слов в ячейках Excel;
• Удалить последние N символов в ячейках столбца;
• Добавить символ, слово или текст во все ячейки столбца;
• Удалить из текста список слов.