Удалить последнее слово из ячейки в Excel 365 проще всего формулой =ТЕКСТДО(A2;" ";-1;;;""): отрицательный номер вхождения −1 означает «последний пробел», и функция возвращает всё, что перед ним. В других версиях поможет формула с ПОДСТАВИТЬ и ДЛСТР (ниже — пошаговый разбор) или регулярное выражение =REGEXREPLACE(A2;"\s+\S+\s*$";""). Удалить последние N слов во всём столбце на месте можно процедурой !SEMTools.
Иногда при работе с Excel возникает необходимость удалить последнее слово или несколько слов в конце фраз в ячейках.
Для решения задачи можно объединить несколько функций программы, чтобы осуществить поиск справа налево в ячейке, но есть помимо обычных формул и другие способы. Расскажу обо всем по порядку.
Удалить последнее слово из ячейки: формула
В отличие от процедуры удаления первого слова из ячейки, где формула довольно простая в силу того, что функция ПОИСК ищет слева направо, формула для удаления последнего слова существенно сложнее.
В Excel 2021 и раньше нет функции, которая искала бы символ справа налево (в Excel 365 эту задачу решает ТЕКСТДО с отрицательным номером вхождения — о ней ниже). Ниже пошаговое описание формулы, которая позволит, тем не менее, сочетанием функций найти его. После этого удалить последнее слово будет уже несложно.
1. Измеряем длину строки.
В этом поможет функция ДЛСТР.
=ДЛСТР(A1)
2. Заменяем пробелы в строке на пустоту.
Здесь поможет функция ПОДСТАВИТЬ.
=ПОДСТАВИТЬ(A1;" ";"")
3. Замеряем длину строки без пробелов.
Формула получается заменой A1 на полное выражение без знака равенства во втором шаге.
=ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))
4. Вычитаем из первой длины вторую, чтобы вычислить таким образом количество пробелов в исходной строке.
=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))
5. Создаём символ, которого в нашем массиве гарантированно нет.
Если вы точно уверены, можно использовать конкретный символ, например, "\".
Можно повысить уверенность с помощью функции ЮНИСИМВ - она позволяет вставлять на лист какие угодно символы из UNICODE-таблицы.
Например:
=ЮНИСИМВ(23456)
Возвращает: 宠
Полагаю, это китайский или японский.
6. Заменяем последний пробел на этот символ.
У функции ПОДСТАВИТЬ есть необязательный четвертый аргумент, обозначающий позицию символа, который нужно заменить. На втором шаге мы его не использовали, чтобы он удалил все пробелы. На этом шаге вставляем в него формулу из четвертого шага.
Третьим аргументом будет формула из пятого шага.
=ПОДСТАВИТЬ(A1;" ";ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")))
7. Находим позицию этого символа.
На этот раз функция ПОИСК замечательно справится. Поскольку символ в строке гарантированно в единственном экземпляре, его можно искать и слева направо.
=ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(A1;" ";ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))
8. Вычитаем из результата единицу, чтобы узнать позицию символа перед ним.
=ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(A1;" ";ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))-1
9. Извлекаем из исходной ячейки текст до этой позиции.
=ЛЕВСИМВ(A1;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(A1;" ";ЮНИСИМВ(23456);ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))))-1)
10. Во всей формуле меняем A1 на " "&A1
=ЛЕВСИМВ(" "&A1;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(" "&A1;" ";ЮНИСИМВ(23456);ДЛСТР(" "&A1)-ДЛСТР(ПОДСТАВИТЬ(" "&A1;" ";""))))-1)
11. «Оборачиваем» результат функцией СЖПРОБЕЛЫ.
Предыдущий шаг обезопасит от ошибок, но добавит лишний пробел слева. Функция удалит его и любые другие лишние пробелы между слов или в конце строки.
Итак, формула удаления последнего слова из ячейки:
=СЖПРОБЕЛЫ(ЛЕВСИМВ(" "&A1;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(" "&A1;" ";ЮНИСИМВ(23456);ДЛСТР(" "&A1)-ДЛСТР(ПОДСТАВИТЬ(" "&A1;" ";""))))-1))
| A | B | |
|---|---|---|
| 1 | Текст | Без последнего слова |
| 2 | купить диван недорого | купить диван=СЖПРОБЕЛЫ(ЛЕВСИМВ(" "&A2;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(" "&A2;" ";ЮНИСИМВ(23456);ДЛСТР(" "&A2)-ДЛСТР(ПОДСТАВИТЬ(" "&A2;" ";""))))-1)) |
| 3 | Иванов Иван Иванович | Иванов Иван=СЖПРОБЕЛЫ(ЛЕВСИМВ(" "&A3;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(" "&A3;" ";ЮНИСИМВ(23456);ДЛСТР(" "&A3)-ДЛСТР(ПОДСТАВИТЬ(" "&A3;" ";""))))-1)) |
| 4 | Москва | =СЖПРОБЕЛЫ(ЛЕВСИМВ(" "&A4;ПОИСК(ЮНИСИМВ(23456);ПОДСТАВИТЬ(" "&A4;" ";ЮНИСИМВ(23456);ДЛСТР(" "&A4)-ДЛСТР(ПОДСТАВИТЬ(" "&A4;" ";""))))-1)) |
Если в ячейке одно слово (A4), формула возвращает пустую строку — последнее слово оно же и единственное. Пробел, приписанный слева (" "&A2), нужен как раз для этого случая.
В английском Excel: =TEXTBEFORE(A2," ",-1,,,"")
Последнее слово в Excel 365: ТЕКСТДО
ТЕКСТДО ищет разделитель и с конца: =ТЕКСТДО(A2;" ";-1;;;"") удалит последнее слово, =ТЕКСТДО(A2;" ";-2;;;"") — два последних. Последний аргумент "" — что вернуть, если пробела нет. Само последнее слово извлекает =ТЕКСТПОСЛЕ(A2;" ";-1;;;A2).
Удалить последнее слово регулярным выражением
Регулярные выражения в Excel наделяют специалиста, знакомого с их синтаксисом, практически неограниченными возможностями по поиску, извлечению, замене и удалению текстовых данных без использования монструозных мегаформул, описанных выше.
Пользователям надстройки !SEMTools формулы регулярных выражений доступны сразу же после подключения надстройки к Excel. Вот так изящно будет выглядеть формула удаления последнего слова с помощью функции REGEXREPLACE:
=REGEXREPLACE(A1;"\s\S+$";"")
Функциями листа можно пользоваться абсолютно бесплатно, а подробно об этом синтаксисе можно почитать в моей статье: «Регулярные выражения в Excel».
Шаблон \s\S+$ означает «пробел, затем непробельные символы до конца строки». Если в конце ячеек бывают лишние пробелы, используйте =REGEXREPLACE(A1;"\s+\S+\s*$";""). Два последних слова удаляет =REGEXREPLACE(A1;"(\s+\S+){2}\s*$";""). В Excel 365 те же шаблоны работают во встроенной функции РЕГЗАМЕНИТЬ.
Удалить последнее слово/N последних слов во всем столбце
Что делать, если не хочется создавать дополнительный столбец и изменить данные прямо на месте? Здесь вам на помощь придет !SEMTools. Ниже показаны примеры удаления последнего слова или двух/трех/N слов в ячейках, прямо на месте с помощью соответствующего меню "Удалить слова" в группе инструментов «УДАЛИТЬ»:
Удаляем последние 2 слова в конце всех ячеек:
Аналогично решается задача для нескольких — можно использовать пункт «Последние N», чтобы указать количество удаляемых слов, считая справа:
Почему последнее слово удаляется неправильно
- Ничего не удалилось. В конце ячейки пробел: формула считает «последним словом» пустоту после него. Сначала удалите лишние пробелы или используйте шаблон \s+\S+\s*$.
- Перед удалённым словом осталась запятая. Формула работает по пробелам: в «Москва, Россия» удалится «Россия», а запятая останется — «Москва,». Удалите пунктуацию заранее или добавьте её в шаблон.
- Ячейки из одного слова стали пустыми. Так формула и работает. Если такие ячейки нужно оставить как есть, в ТЕКСТДО укажите последним аргументом A2 вместо "".
- ТЕКСТДО даёт #ИМЯ?. Функция есть только в Microsoft 365 и Excel 2024.
Удаление последнего слова — частые вопросы
Как удалить последнее слово в ячейке Excel?
В Excel 365 — формулой =ТЕКСТДО(A1;" ";-1;;;""), в других версиях — формулой с ПОДСТАВИТЬ и ДЛСТР из статьи или REGEXREPLACE из !SEMTools.
Как удалить последнее слово регулярным выражением?
=REGEXREPLACE(A1;"\s+\S+\s*$";"") — шаблон находит последний пробел и всё после него до конца строки.
Как удалить последние два слова?
=ТЕКСТДО(A1;" ";-2;;;"") в Excel 365 или =REGEXREPLACE(A1;"(\s+\S+){2}\s*$";"").
Как извлечь последнее слово, а не удалить?
Формулой =ТЕКСТПОСЛЕ(A1;" ";-1;;;A1) в Excel 365.
Удалить последнее слово или последние N слов сразу во всех выделенных ячейках — прямо на месте, без формул и дополнительных столбцов — можно в 2 клика с !SEMTools.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: как удалить последние слова
Надеюсь, эта статья помогла вам, и вы с уверенностью сможете удалить последние слова из ячеек. Если хотите делать это, не прибегая к сложным формулам, станьте пользователем надстройки для Excel.
Смотрите похожие разделы: