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

Обновлено: 02.07.2024

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

Удалить первое слово из текстовой строки

Если вы хотите удалить первое слово из текстовой строки в ячейках Excel, вы можете использовать формулу, основанную на функциях ПРАВИЛЬНО, ДЛСТР и НАЙТИ. Вот так:

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

Удалить последнее слово из текстовой строки

Если вы хотите удалить последнее слово из текстовой строки, вы можете использовать формулу, основанную на функциях ЛЕВЫЙ, ОБРЕЗАТЬ, НАЙТИ и ПОДСТАВИТЬ. Вот так:

Связанные функции

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

Предположим, что вы перечислили некоторый текст в одной ячейке, разделенной разрывом строки (вы можете сделать это, нажав ALT + ENTER после ввода текста), и теперь вы хотите извлечь несколько строк текста.

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

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

В этом посте рассказывается, как подсчитывать значения в одной ячейке, разделенные запятыми, с помощью формулы в Excel. Как подсчитать значения, разделенные запятыми, в одной ячейке в Excel 2013/2016. Можно ли иметь .

В этом посте рассказывается, как извлечь адрес электронной почты из текстовой строки в Excel. Как использовать формулу для извлечения адреса электронной почты в Excel. Как извлечь адрес электронной почты из текстовой строки с помощью макроса VBA в формате .

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

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

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

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

Формула Excel для удаления первого или последнего слова из ячейки.

Вы можете скопировать и вставить приведенные ниже формулы для быстрого исправления, а также узнать, как они работают, если вам интересно.

Разделы:

Удалить первое слово из ячейки

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

Символы без пробелов

Если в ваших словах используются тире или какой-либо другой разделитель, просто замените " " любым разделителем. Если используется дефис, измените его на "-" в формуле.

Пояснение

Функция ПРАВИЛЬНО получает текст справа от ячейки. Это необходимо, потому что мы извлекаем весь текст, кроме первого слова; это означает, что нам нужно получить все, что находится справа от первого слова в ячейке.

Функции ДЛСТР и НАЙТИ используются, чтобы сообщить функции ПРАВИЛЬНО, сколько символов справа от ячейки мы хотим получить. Функция ПРАВИЛЬНО извлекает только определенное количество символов справа от ячейки.

Чтобы получить все, кроме первого слова, нам нужно найти пробел, который отделяет первое слово от остального текста. Используйте функцию НАЙТИ, ища пробел " ", чтобы получить количество символов слева от ячейки до первого пробела. В приведенном выше примере это возвращает 5. Это количество символов, которые мы хотим удалить из ячейки.

Чтобы получить число, которое мы хотим сохранить, нам нужно использовать функцию ДЛСТР для подсчета всех символов в ячейке. Результат функции ДЛСТР равен 12, поскольку в ячейке 12 символов.

В приведенной выше формуле мы вычитаем результат НАЙТИ из результата ДЛСТР, который равен 12-5 или 7.

Это указывает функции ПРАВИЛЬНО получить 7 символов справа от ячейки, что эффективно удаляет первое слово из ячейки.

Удалить последнее слово из ячейки

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

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

Символы без пробелов

Пояснение

В этой формуле используются четыре функции: НАЛЕВО(), ДЛСТР(), НАЙТИ(), ПОДСТАВИТЬ().

Это может немного сбить с толку, но вам не нужно читать этот раздел, чтобы использовать формулу.

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

Для этого нам нужно сообщить функции ВЛЕВО, сколько символов слева от ячейки мы хотим в новой ячейке, и это число может варьироваться. Сложная часть формулы — это то, что дает нам это число.

Эта часть формулы ПОДСТАВИТЬ(A1," ","") удаляет все пробелы в ячейке, а затем подсчитывает количество оставшихся символов с помощью функции ДЛСТР ДЛСТР(ПОДСТАВИТЬ(A1," ","")). Из приведенного выше примера результат равен 10.

Затем подсчитывается количество символов в ячейке, когда в ней остаются пробелы: LEN(A1). Результат равен 12.

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

Используя только что полученное число, снова используется функция ПОДСТАВИТЬ, чтобы заменить последний пробел в ячейке на ~, а затем используется функция НАЙТИ, чтобы найти это ~, и затем формула знает, сколько символов слева от ячейка последний пробел.

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

Теперь у нас есть общее количество символов слева от ячейки до пробела перед последним словом в ячейке, и мы можем использовать функцию ВЛЕВО, чтобы вернуть все, кроме последнего слова.

Если вы все еще немного запутались, ознакомьтесь с нашим руководством по функции ПОДСТАВИТЬ в Excel

Более универсальные формулы — предотвращение ошибок

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

Чтобы избежать ошибок, мы можем использовать простую функцию ЕСЛИОШИБКА().

Формула 1:

Формула 2:

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

Прочитайте наш учебник, чтобы узнать больше о функции ЕСЛИОШИБКА в Excel и подавлении ошибок в целом.

Примечания

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

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

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

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

Формулы для удаления первого или последнего символа из ячейки в Excel
Учебное пособие: формулы, позволяющие быстро и легко удалить первый или последний символ из ячейки.

Извлечение последнего слова из ячейки в Excel — Извлечение текста с разделителем, определяемым пользователем — UDF
Макрос: эта UDF (определяемая пользователем функция) извлекает последнее слово или символы из ячейки в Excel.

Получить первое слово из ячейки в Excel
Учебник: Как использовать формулу для получения первого слова из ячейки в Excel. Это работает для одного c.

Извлечение первого слова из ячейки в Excel — Извлечение текста с разделителем, определяемым пользователем — UDF
Макрос: эта бесплатная функция Excel UDF (определяемая пользователем функция) возвращает первое слово из ячейки в Exce.

Удалить все проверки данных из ячейки в Excel
Макрос: удалить все проверки данных из ячейки в Excel с помощью этого бесплатного макроса Excel. Это здорово.

Удалить все пустые строки или пустые ячейки из диапазона в Excel
Учебник: Как быстро удалить все пустые ячейки или строки из диапазона в Excel. Это позволяет вам .

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков
50+ часов видео
200+ руководств по Excel

< /p>

Удалить первое или последнее слово из строки

В этом руководстве объясняется, как удалить только первое или последнее слово из текстовой строки в ячейке с формулами в Excel.

Удалить первое слово из строки в ячейке

В этом разделе вы узнаете, как использовать комбинацию функций ПРАВО, ДЛСТР и НАЙТИ для удаления первого слова из строки в ячейке.

Общая формула

Аргументы

A1: представляет ячейку, содержащую текстовую строку, из которой вы удалите первое слово.

Как использовать эту формулу?

<р>1. Выберите пустую ячейку для вывода результата.

<р>2. Введите в него приведенную ниже формулу и нажмите клавишу Enter.Затем перетащите маркер заполнения вниз, чтобы применить формулу к другим ячейкам.

=ПРАВО(B3,ДЛСТР(B3)-НАЙТИ(" ",B3))

Примечание. В этой формуле B3 — это ячейка, содержащая текстовую строку, из которой нужно удалить первое слово. Измените его по своему усмотрению.

Как работает эта формула?

=ВПРАВО(B3,ДЛСТР(B3)-НАЙТИ(" ",B3))

<р>1. ДЛСТР(B3): функция ДЛСТР вычисляет общую длину текстовой строки «Mr ana varela (Home)» и возвращает результат 20;

<р>2. НАЙТИ(" ",B3): Поскольку слова разделены пробелом, здесь функция НАЙТИ находит позицию первого пробела в текстовой строке "Mr ana varela (Home)" и возвращает результат как 3;

<р>3. ВПРАВО(B3,20-3): Функция ВПРАВО извлекает 17 символов (20-3=17) с правой стороны текстовой строки «Mr ana varela (Home)». В результате получится "ana varela (Home)".

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

Если вы хотите удалить последнее слово из текстовой строки в ячейке, вы можете сделать следующее.

Общая формула

=LEFT(ОТРЕЗАТЬ(A1),НАЙТИ("~",ЗАМЕНИТЬ(A1," ","~",ДЛСТР(ОТРЕЗАТЬ(A1))-ДЛСТР(ЗАМЕНИТЬ(ОТРЕЗАТЬ(A1)," ","" ))))-1)

Аргументы

A1: представляет ячейку, содержащую текстовую строку, из которой вы удалите последнее слово.

Как использовать эту формулу?

<р>1. Выберите пустую ячейку, чтобы поместить результат. В данном случае я выбираю ячейку D3.

<р>2. Введите в него приведенную ниже формулу и нажмите клавишу Enter. Выберите ячейку результата и перетащите ее маркер заполнения вниз, чтобы применить формулу к другим ячейкам.

=ЛЕВО(ОТРЕЗАТЬ(B3),НАЙТИ("~",ПОДСТАВИТЬ(B3," ","~",ДЛСТР(ОТРЕЗАТЬ(B3))-ДЛСТР(ЗАМЕНИТЬ(ОТРЕЗАТЬ(B3)," ","" ))))-1)

Примечание. В формуле B3 – это ячейка, содержащая текстовую строку, из которой нужно удалить последнее слово. Измените его по своему усмотрению.

Как работает эта формула?

=ЛЕВО(ОТРЕЗАТЬ(B3),НАЙТИ("~",ПОДСТАВИТЬ(B3," ","~",ДЛСТР(ОТРЕЗАТЬ(B3))-ДЛСТР(ЗАМЕНИТЬ(ОТРЕЗАТЬ(B3)," ","" ))))-1)

<р>1. TRIM(B3): Функция TRIM удаляет все лишние пробелы из строки «Mr ana varela (Home)» и оставляет только один пробел между словами. Здесь результат "Мистер ана варела (Дом)";

<р>2. НАЙТИ("~",ПОДСТАВИТЬ(B3," ","~",ДЛСТР(ОТРЕЗАТЬ(B3))-ДЛСТР(ПОДСТАВИТЬ(ОТРЕЗАТЬ(B3)," ",""))))

LEN(TRIM(B3)) : Поскольку функция TRIM возвращает результат в виде "Mr ana varela (Home)", функция LEN возвращает количество символов в строке "Mr ana varela (Home)" и возвращает результат 20;
LEN(SUBSTITUTE(TRIM(B3)," ","")): функция ПОДСТАВИТЬ заменяет все пробелы в текстовой строке "Mr ana varela (Home)" ничем и возвращает результат как "Mranavarela(Home)" . Затем функция LEN вычисляет общую длину «Mranavarela(Home)» и получает результат 17;
ПОДСТАВИТЬ(B3," ","~",20-17): Здесь функция ПОДСТАВИТЬ заменяет третий пробел (20-17=3) в текстовой строке "Г-н ана варела (Дом)" на символ "~ ". Здесь результат "Mr ana varela~(Home)".
НАЙТИ("~","Г-н ана варела~(Дом)") : функция НАЙТИ возвращает позицию символа "~" в текстовой строке "Г-н ана варела(Дом)". Здесь результат равен 14.

<р>3. ВЛЕВО("Г-н ана варела (Дом)",14-1) : Функция ВЛЕВО извлекает 13 символов с левой стороны текстовой строки "Г-н ана варела (Дом)". И конечный результат — г-н ана варела.

Связанные функции

Функция Excel ПРАВИЛЬНО
Функция Excel ПРАВИЛЬНО извлекает определенное количество символов с правой стороны текстовой строки.

Функция ДЛСТР в Excel
Функция ДЛСТР в Excel возвращает количество символов в текстовой строке.

Функция Excel FIND
Функция Excel FIND находит строку в другой строке и возвращает начальную позицию строки внутри другой.

Функция Excel ВЛЕВО
Функция Excel ВЛЕВО извлекает заданное количество символов из левой части предоставленной строки.

Функция Excel TRIM
Функция Excel TRIM удаляет все лишние пробелы из текстовой строки и оставляет только одиночные пробелы между словами.

Функция Excel ПОДСТАВИТЬ
Функция Excel ПОДСТАВИТЬ заменяет текст или символы в текстовой строке другим текстом или символами.

Связанные формулы

Удалить расширение из имени файла
В этом руководстве представлена формула с подробными инструкциями, которые помогут вам удалить расширение из имени файла в Excel.

Удалить первые N символов из ячейки
В этом руководстве представлены две формулы, которые помогут вам легко удалить первые n символов из ячейки в Excel.

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

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

Удалить текст из ячейки на основе определенной позиции
В этом руководстве объясняется, как использовать формулы для удаления текста из ячейки на основе определенной позиции в Excel.

Лучшие инструменты для повышения производительности в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите выполнять свою повседневную работу быстро и качественно? Kutools for Excel предоставляет мощные расширенные функции 300 (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.

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

Как удалить символы слева в Excel

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

Удалить первый символ в Excel

Чтобы удалить первый символ из строки, вы можете использовать либо функцию REPLACE, либо комбинацию функций RIGHT и LEN.

Здесь мы просто берем 1 символ из первой позиции и заменяем его пустой строкой ("").

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

Например, чтобы удалить первый символ из ячейки A2, формулы выглядят следующим образом:

=ВПРАВО(A2, ДЛСТР(A2) - 1)

Удалить символы слева

Чтобы удалить начальные символы с левой стороны строки, вы также используете функции REPLACE или RIGHT и LEN, но каждый раз указываете, сколько символов вы хотите удалить:

=ВПРАВО(A2, ДЛСТР(A2) - 2)

Чтобы удалить первые 3 символа, формулы принимают следующий вид:

=ВПРАВО(A2, ДЛСТР(A2) - 3)

На приведенном ниже снимке экрана показана формула REPLACE в действии. С RIGHT LEN результаты будут точно такими же.

Пользовательская функция для удаления первых n символов

Если вы не возражаете против использования VBA на своих листах, вы можете создать собственную пользовательскую функцию для удаления символов с начала строки с именем RemoveFirstChars. Код функции очень прост:

После того, как код вставлен в книгу (подробные инструкции здесь), вы можете удалить первые n символов из данной ячейки, используя эту компактную и интуитивно понятную формулу:

Например, чтобы удалить первый символ из строки в A2, используйте формулу в B2:

Чтобы удалить первые два символа из A3, используйте формулу в B3:

Чтобы удалить первые три символа из A4, используйте формулу в B4:

Как удалить символы справа

Чтобы удалить символы с правой стороны строки, вы также можете использовать встроенные функции или создать свою собственную.

Удалить последний символ в Excel

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

В этой формуле вы вычитаете 1 из общей длины строки и передаете разницу в функцию ВЛЕВО, чтобы она извлекла столько символов из начала строки.

Например, чтобы удалить последний символ из ячейки A2, используйте формулу в ячейке B2:

=ЛЕВО(A2, ДЛСТР(A2) - 1)

Удалить символы справа

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

Логика та же, что и в приведенной выше формуле, и ниже приведены несколько примеров.

Чтобы удалить последние 3 символа, используйте 3 вместо num_chars:

=ЛЕВО(A2, ДЛСТР(A2) - 3)

Чтобы удалить последние 5 символов, укажите 5 для num_chars:

=ЛЕВО(A2, ДЛСТР(A2) - 5)

Пользовательская функция для удаления последних n символов в Excel

Если вы хотите иметь собственную функцию удаления любого количества символов справа, добавьте этот код VBA в свою книгу:

Функция называется RemoveLastChars, и ее синтаксис вряд ли нуждается в пояснениях:

Чтобы проверить это в полевых условиях, давайте избавимся от последнего символа в A2:

Кроме того, мы удалим последние 2 символа с правой стороны строки в A3:

Чтобы удалить последние 3 символа из ячейки A4, используйте следующую формулу:

Как вы можете видеть на снимке экрана ниже, наша пользовательская функция работает блестяще!

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

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

chars_left — количество символов, которые необходимо удалить слева.
chars_right — количество символов, которые необходимо удалить справа.

Подайте приведенные выше числа в формулу:

=СРЕДН(A2, 7+1, ДЛСТР(A2) - (7+10))

…и результат не заставит себя ждать:

Чтобы понять, что здесь происходит на самом деле, давайте вспомним синтаксис функции MID, которая используется для извлечения подстроки определенного размера из середины исходной строки:

Аргумент text не вызывает вопросов — это исходная строка (в нашем случае A2).

Чтобы получить позицию первого извлекаемого символа (start_num), вы добавляете 1 к количеству символов, которые нужно удалить слева (7+1).

Чтобы определить, сколько символов нужно вернуть (num_chars), вы вычисляете общее количество удаленных символов (7 + 11) и вычитаете сумму из длины всей строки: LEN(A2) - (7+10)).

Получить результат в виде числа

Какую бы из приведенных выше формул вы ни использовали, на выходе всегда будет текст, даже если возвращаемое значение содержит только числа. Чтобы вернуть результат в виде числа, либо заключите основную формулу в функцию ЗНАЧ, либо выполните какую-либо математическую операцию, которая не влияет на результат, например. умножьте на 1 или прибавьте 0. Этот метод особенно полезен, когда вы хотите дополнительно вычислить результаты.

Предположим, вы удалили первый символ из ячеек A2:A6 и хотите просуммировать полученные значения. Удивительно, но тривиальная формула SUM возвращает ноль. Почему это? Очевидно, потому что вы складываете строки, а не числа. Выполните одну из следующих операций, и проблема будет устранена!

=ЗНАЧ(ЗАМЕНИТЬ(A2, 1, 1, ""))

=ВПРАВО(A2, ДЛСТР(A2) - 1) * 1

=Удалить первые символы (A2, 1) + 0

Удалить первый или последний символ с помощью Flash Fill

В Excel 2013 и более поздних версиях есть еще один простой способ удалить первый и последний символы в Excel — функция мгновенного заполнения.

В ячейке, смежной с первой ячейкой с исходными данными, введите желаемый результат, опуская первый или последний символ исходной строки, и нажмите Enter .
Начните вводить ожидаемое значение в следующей ячейке. Если Excel обнаружит шаблон в данных, которые вы вводите, он будет следовать тому же шаблону в остальных ячейках и отобразит предварительный просмотр ваших данных без первого/последнего символа.
Просто нажмите клавишу Enter, чтобы принять предварительный просмотр.

Удаление символов по позиции с помощью Ultimate Suite

Традиционно пользователи нашего Ultimate Suite могут справиться с этой задачей в несколько кликов, не запоминая множество различных формул.

Чтобы удалить первые или последние n символов из строки, вам нужно сделать следующее:

Например, чтобы удалить первый символ, мы настраиваем следующую опцию:

Вот как удалить подстроку слева или справа в Excel. Я благодарю вас за чтение и с нетерпением жду встречи с вами в нашем блоге на следующей неделе!

Читайте также: