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

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

Пусть текстовая строка Василий Иванович Петров находится в ячейке A 1 . Выведем последнее слово (см. файл примера ): =ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(«^^»; ПОДСТАВИТЬ(A1;» «;»^^»;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;» «;»»)))))

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

В статье Выбор из текстовой строки n-го слова приведено решение подобной задачи в общем случае.

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

Как альтернативу можно посоветовать воспользоваться инструментом Текст-По-Столбцам ( Данные/Работа с данными ), позволяющему, разделить текстовую строку на несколько строк.

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

Функция Excel для удаления двух последних слов из ячейки

Я хочу удалить последние два слова, то есть 4m 44s из каждой ячейки, чтобы результат был:

Использование операторов для простой арифметики

создание новой базы данных

но эта функция удаляет только последнее слово. Как удалить последние два слова из каждой ячейки?

2 ответа 2

Возможно, у вас в конце текста есть пробелы, потому что, как говорит Excellll, все должно работать иначе. Попробуйте добавить некоторые функции TRIM чтобы удалить завершающие пробелы ( TRIM также удалит все начальные пробелы и превратит любые двойные внутренние пробелы в одиночные пробелы . но я предполагаю, что это не будет проблемой?)

=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1),» «,CHAR(1),LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),» «,»»))-1)))

или вот другой (более короткий) способ,

=TRIM(LEFT(A1,FIND(TRIM(RIGHT(SUBSTITUTE(TRIM(A1),» «,REPT(» «,9)),18)),A1)-1))

Вот формула, которая сделает это. Это немного долго, но это сделает свое дело:

Формула работает, используя функцию SUBSTITUTE для замены пробела следующего за последним на какой-то непонятный текст для замены, который вряд ли можно найти где-либо еще в тексте («@@ REPLACE @@» в примере). Тогда функция LEFT может вернуть все слева от текста замены. Причина для этого заключается в том, что FIND не позволяет вам указывать экземпляр пробела, а вместо этого отправляет начальную позицию, что в данном случае бесполезно. SUBSTITUTE однако, позволяет вам указать номер экземпляра пространства, поэтому он и используется.

Ожидайте ошибку, которая будет возвращена для текста с двумя или менее словами.

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

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

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

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

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

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

1. Измеряем длину строки.
В этом поможет функция ДЛСТР.

2. Заменяем пробелы в строке на пустоту.
Здесь поможет функция ПОДСТАВИТЬ.

3. Замеряем длину строки без пробелов.
Формула получается заменой A1 на полное выражение без знака равенства во втором шаге.

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

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

Возвращает: 宠
Полагаю, это китайский или японский.

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

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

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

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

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

11. «Оборачиваем» результат функцией СЖПРОБЕЛЫ.
Предыдущий шаг добавит обезопасит от ошибок, но добавит лишний пробел слева. Функция удалит его и любые другие лишние пробелы между слов или в конце строки.

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

Удаление последнего слова в таблице Excel

Удаление последнего слова в Excel — это простая задача, выполнить которую можно при помощи приведенной ниже формулы, которая также поможет извлечь (копировать) последние слова в новую ячейку.

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

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

Удаление последнего слова из ячейки в таблице Excel

Все готово, задача выполнена. Аналогично можно избавиться и от начальных значений.

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

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

Извлечение последнего слова из ячейки в таблице Эксель

Если потребуется больше операций в данном направлении ознакомьтесь со статьей.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *