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

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

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

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

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

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

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

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

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

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

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

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

Как удалить весь текст из ячейки Excel после определенного знака

Функция ПРАВСИМВ в Excel — примеры и советы.

Текст (обязательно) — текст, из которого вы хотите извлечь символы.
число_знаков (необязательно) — количество символов для извлечения, начиная с самого правого символа.

Если аргумент опущен, возвращается один последний символ (по умолчанию).
Когда число знаков для извлечения больше, чем общее количество символов в ячейке, возвращается весь текст.
Если введено отрицательное число, формула возвращает ошибку #ЗНАЧ!.

Подсчитайте количество разделителей в исходной строке. Это несложно:
Вычисляете общую ее длину с помощью ДЛСТР(A2).
Определяем длину без разделителей, используя формулу ПОДСТАВИТЬ, которая заменяет все вхождения двоеточия ничем: ДЛСТР(ПОДСТАВИТЬ(A2; «:»; «»))
Наконец, вы вычитаете длину исходной строки без разделителей из общей длины: ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2;»:»;»»))

Замените последний разделитель на какой-нибудь уникальный символ. Чтобы извлечь текст, который идет после последнего разделителя, нам нужно каким-то образом «пометить» это последнее вхождение разделителя. Для этого давайте заменим последнее вхождение двоеточия символом, который нигде не встречается в исходных значениях, например, знаком доллара ($).

Определяем позицию последнего разделителя. В зависимости от того, на какой символ вы заменили последний разделитель, используйте ПОИСК (без учета регистра) или НАЙТИ (с учетом регистра), чтобы определить позицию этого символа. Мы заменили последнее двоеточие на знак $, поэтому используем следующую формулу, чтобы узнать его местоположение:

Получаем подстроку справа от последнего разделителя. Теперь, когда вы знаете позицию последнего разделителя, все, что вам нужно сделать, это вычесть это число из общей длины строки и использовать ПРАВСИМВ, чтобы вернуть это количество символов из конца исходного текста:

Может ли функция Excel ПРАВСИМВ вернуть число?

Функция ДЕНЬ для извлечения дня: = ДЕНЬ(A1)
Функция МЕСЯЦ, чтобы получить месяц: = МЕСЯЦ(A1)
ГОД, чтобы вытащить год: = ГОД(A1)

В исходных данных есть один или несколько концевых пробелов. Чтобы быстро удалить лишние пробелы в ячейках, используйте функцию СЖПРОБЕЛЫ.
Аргумент число_знаковменьше нуля. Конечно, вряд ли вам захочется специально добавлять отрицательное число в формулу, но если этот аргумент вычисляется какой-то формулой Excel, и вы получаете ошибку #ЗНАЧ!, то обязательно проверьте вложенные выражения на наличие ошибок.
Исходное значение — дата. Если вы внимательно следовали этому руководству, вы уже знаете, почему формула ПРАВСИМВ не может работать с датами.

Как удалить пробелы в ячейках Excel — Вы узнаете, как с помощью формул удалять начальные и конечные пробелы в ячейке, лишние интервалы между словами, избавляться от неразрывных пробелов и непечатаемых символов. В чем самая большая проблема с…
Функция СЖПРОБЕЛЫ — как пользоваться и примеры — Вы узнаете несколько быстрых и простых способов, чтобы удалить начальные, конечные и лишние пробелы между словами, а также почему функция Excel СЖПРОБЕЛЫ (TRIM в английской версии) не работает и как…
Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…
Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов — ПСТР — одна из текстовых функций, которые Microsoft Excel предоставляет для управления текстовыми строками. На самом базовом уровне она используется для извлечения подстроки из середины текста. В этом руководстве мы обсудим…
5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…
Как быстро сосчитать количество символов в ячейке Excel — В руководстве объясняется, как считать символы в Excel. Вы изучите формулы, позволяющие получить общее количество символов в диапазоне и подсчитывать только определенные символы в одной или нескольких ячейках. В нашем предыдущем…
Как в Excel разделить текст из одной ячейки в несколько — В руководстве объясняется, как разделить ячейки в Excel с помощью формул и стандартных инструментов. Вы узнаете, как разделить текст запятой, пробелом или любым другим разделителем, а также как разбить строки на…
Как преобразовать текст в число в Excel — 10 способов. — В этом руководстве показано множество различных способов преобразования текста в число в Excel: опция проверки ошибок в числах, формулы, математические операции, специальная вставка и многое другое. Иногда значения в ваших…

Как удалить текст до или после определенного символа в Excel

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

Ниже приведены шаги для этого:

Скопируйте и вставьте данные из столбца A в столбец B (это также необходимо для сохранения исходных данных)
Выделив ячейки в столбце B, перейдите на вкладку «Главная».
В группе «Редактирование» нажмите «Найти и выбрать».
В параметрах, которые появляются в раскрывающемся списке, щелкните параметр «Заменить». Откроется диалоговое окно «Найти и заменить».
В поле «Найти» введите * (т. Е. Запятую, за которой следует звездочка).
Оставьте поле «Заменить на» пустым.
Нажмите кнопку «Заменить все».

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

Ниже приведена формула для этого:

В приведенной выше формуле используется функция FIND (НАЙТИ), чтобы найти положение запятой в ячейке.

Вот формула, которая сделает это:

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

В этом случае мне нужно как-то определить позицию последнего вхождения запятой, а затем извлечь все, что находится слева от нее.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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