Удалить последнее слово в ячейке excel
Обновлено: 03.07.2024
В этой статье описаны синтаксис формулы и использование функций ПРАВСИМВ иПРАВБ в Microsoft Excel.
Описание
Функция ПРАВСИМВ возвращает последний символ или несколько последних символов текстовой строки на основе заданного числа символов.
Функция ПРАВБ возвращает последний символ или несколько последних символов текстовой строки на основе заданного числа байтов.
Эти функции могут быть доступны не на всех языках.
Функция ПРАВСИМВ предназначена для языков с однобайтовой кодировкой, а ПРАВБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.
Функция ПРАВСИМВ всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.
Функция ПРАВБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ПРАВБ считает каждый символ за один.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ПРАВСИМВ и ПРАВБ описаны ниже.
Текст Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.
Число_знаков Необязательный. Количество символов, извлекаемых функцией ПРАВСИМВ.
Значение "число_знаков" должно быть больше нуля или равно ему.
Если значение "число_знаков" превышает длину текста, функция ПРАВСИМВ возвращает весь текст.
Если значение "число_знаков" опущено, оно считается равным 1.
Число_байтов Необязательный. Количество символов, извлекаемых функцией ПРАВБ.
Num_bytes должен быть больше нуля или равен нулю.
Если num_bytes больше, чем длина текста, то right возвращает весь текст.
Если num_bytes опущен, предполагается, что это 1.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
В этом руководстве объясняется, как удалить только первое или последнее слово из текстовой строки в ячейке с формулами в Excel.
Удалить первое слово из строки в ячейке
В этом разделе вы узнаете, как использовать комбинацию функций RIGHT, LEN и FIND для удаления первого слова из строки в ячейке.
Общая формула
аргументы
A1: Представляет ячейку, содержащую текстовую строку, из которой вы удалите первое слово.
Как пользоваться этой формулой?
1. Выберите пустую ячейку для вывода результата.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите его маркер заполнения вниз, чтобы применить формулу к другим ячейкам.
=RIGHT(B3,LEN(B3)-FIND(" ",B3))
Внимание: В этой формуле B3 - это ячейка, содержащая текстовую строку, из которой вы хотите удалить первое слово. Пожалуйста, измените его по своему усмотрению.
Как работает эта формула?
=RIGHT(B3,LEN(B3)-FIND(" ",B3))
1. LEN(B3) : Функция LEN вычисляет общую длину текстовой строки «Mr ana varela (Home)» и возвращает результат как 20;
2. FIND(" ",B3) : Поскольку слова разделены пробелом, здесь функция НАЙТИ определяет положение первого пробела в текстовой строке «Mr ana varela (Home)» и возвращает результат как 3;
3. RIGHT(B3,20-3) : Функция ВПРАВО извлекает 17 символов (20-3 = 17) из правой части текстовой строки «Mr ana varela (Home)». Результат - «ана варела (Дом)».
Удалить последнее слово из строки в ячейке
Если вы хотите удалить последнее слово из текстовой строки в ячейке, вы можете сделать следующее.
Общая формула
аргументы
A1: Представляет ячейку, содержащую текстовую строку, из которой вы удалите последнее слово.
Как пользоваться этой формулой?
1. Выберите пустую ячейку, чтобы разместить результат. В данном случае я выбираю ячейку D3.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ. Выберите ячейку результата и перетащите ее маркер заполнения вниз, чтобы применить формулу к другим ячейкам.
Внимание: В формуле B3 - это ячейка, содержащая текстовую строку, из которой вы хотите удалить последнее слово. Пожалуйста, измените его по своему усмотрению.
Как работает эта формула?
1. TRIM(B3) : Функция TRIM удаляет все лишние пробелы из строки «Mr ana varela (Home)» и сохраняет только один пробел между словами. Результатом будет «Мистер ана варела (Дом)»;
- 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;
- SUBSTITUTE(B3," ","
",20-17) : Здесь функция ЗАМЕНА заменяет третий пробел (20-17 = 3) в текстовой строке «Mr ana varela (Home)» на символ «
». Результатом будет «Mr ana varela
(Home)") : Функция НАЙТИ возвращает позицию символа «
3. LEFT("Mr ana varela (Home)",14-1) : Функция LEFT извлекает 13 символов из левой части текстовой строки «Mr ana varela (Home)». И конечный результат - г-н ана варела.
Связанные функции
Функция Excel ВПРАВО
Функция Excel RIGHT извлекает определенное количество символов из правой части текстовой строки.
Функция ДЛСТР в Excel
Функция Excel LEN возвращает количество символов в текстовой строке.
Функция поиска в Excel
Функция Excel FIND находит строку в другой строке и возвращает начальную позицию строки внутри другой.
Функция ВЛЕВО в Excel
Функция Excel LEFT извлекает заданное количество символов из левой части предоставленной строки.
Функция Excel TRIM
Функция Excel TRIM удаляет все лишние пробелы из текстовой строки и сохраняет только отдельные пробелы между словами.
Функция ПОДСТАВИТЬ в Excel
Функция ЗАМЕНА в Excel заменяет текст или символы в текстовой строке другим текстом или символами.
Родственные формулы
Удалить расширение из имени файла
В этом руководстве подробно описана формула, которая поможет вам удалить расширение из имени файла в Excel.
Удалить первые N символов из ячейки
В этом руководстве представлены две формулы, которые помогут вам легко удалить первые n символов из ячейки в Excel.
Удаление разрывов строк из ячеек в Excel
В этом руководстве представлены три формулы, которые помогут вам удалить разрывы строк (которые возникают при нажатии клавиш Alt + Enter в ячейке) из определенных ячеек в Excel.
Удалить текст из ячейки, сопоставив содержимое
В этой статье показано, как использовать функцию ЗАМЕНА для удаления части текстовой строки из указанных ячеек путем сопоставления содержимого.
Удалить текст из ячейки на основе определенной позиции
В этом руководстве объясняется, как использовать формулы для удаления текста из ячейки на основе определенной позиции в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Чтобы удалить последнее слово из текстовой строки, вы можете использовать формулу, основанную на функции MID, с помощью функций SUBSTITUTE, LEN и FIND. В показанном примере формула в ячейке B5:
Объяснение
Эта формула использует функцию MID для удаления последнего слова из текстовой строки. Основная задача - выяснить, где начинается последнее слово.
Формула немного запутана, но шаги просты. Сначала мы подсчитываем, сколько пробелов существует в тексте, используя LEN и SUBSTITUTE. Затем мы используем несколько непонятный аргумент «instance» в функции SUBSTITUTE, чтобы заменить последний пробел тильдой (
). Наконец, мы используем FIND, чтобы выяснить, где находится тильда, и функцию MID, чтобы отбросить все, что находится после тильды.
Работая наизнанку, мы используем функции LEN и SUBSTITUTE, чтобы подсчитать, сколько пробелов появляется в следующем:
Для текста в B5 это возвращает 6. Эта страница объясняет эту часть формулы более подробно. Это говорит нам, что мы хотим вырезать текст до шестого пробела.
Примечание: если слова разделены более чем одним пробелом, вам нужно сначала нормализовать пробелы с помощью функции TRIM.
Затем число 6 вставляется в другую ЗАМЕНА как "номер экземпляра":
Здесь мы заменяем 6-й экземпляр пробела ("") тильдой (
). После запуска SUBSTITUTE он возвращает эту строку:
Примечание: мы используем тильду (
) только потому, что это редко встречающийся символ. Вы можете использовать любой символ, который вам нравится, если он не появляется в исходном тексте.
Затем мы используем функцию НАЙТИ, чтобы найти тильду:
НАЙТИ возвращает 34, поскольку тильда - это 34-й символ. Мы вычитаем 1 из этого числа, потому что мы не хотим включать последний пробел в окончательный результат. Теперь мы можем упростить формулу до:
Затем функция MID возвращает символы 1-33:
С настраиваемым разделителем
Та же формула может использоваться с другим разделителем. Например, чтобы удалить весь текст после последней косой черты "/", вы можете использовать:
Удалить последние n слов
Вы можете адаптировать формулу, чтобы удалить последние 2 слова, последние 3 слова и т. Д. Общая форма:
где d - разделитель, а n - количество слов, которые нужно удалить.
Чисто теоретически это задача на строки при помощи макроса VBA. В цикле ищем второй пробел с конца, ставим маркер на 1 символ вправо и с этой позиции по конце фразы копируем текст в отдельную ячейку.
Как провернуть через формулы я ХЗ
Сергей Явин,
Да нет, несложная. Хотя и нетипичная. В VBA делается за несколько минут.
Можно было бы и формулами, но тогда не получится именно переместить В последнюю ячейку.
А так вариант решения через формулы:
- найти n-ный пробел от конца строки в ячейке (FIND)
- отрезать все слева от него, поместить в другой столбец (LEFT)
- отрезать все справа от него, поместить в третий столбец (RIGHT)
Тогда будут столбцы с искомыми данными, просто исходный останется в оригинальном виде.
=ЕСЛИОШИБКА(ПРАВСИМВ(RC[-1];ДЛСТР(RC[-1])-ПОИСК(" ";RC[-1]; ПОИСК(" ";RC[-1])+1));"")
Здесь RC[-1] - соседняя (слева от клетки с формулой) клетка. В клетку с формулой копируется все что после первых двух пробелов (двух слов). Если нужно переносить меньше слов, вложить во второй "ПОИСК" еще один поиск, в качестве 3-го параметра (стартовой позиции для поиска), етц.
Вам нужна поддержка регулярных выражений. По умолчанию её нет в Excel.
- Использовать Google Sheets, там есть поддержка (например, функция REGEXMATCH)
- Добавить поддержку в Excel, для этого нужно погуглить "excel регулярные выражения". Здесь расписывать нет смысла, т.к. уже всё расписано в других местах.
Само выражение зависит от того, что вы хотите конкретно. Ведь 2 или 3 слова - это не однозначная формула. Так 2 слова или 3 слова нужно? Или 50/50 случайным образом? Или 3 слова, когда есть 3, а когда нет 3, но есть 2, тогда 2? А что если всего 1 слово, какой должен быть результат? И так далее.
Для двух последних слов выражение примерно такое:
\s+(\S+\s+\S+)\s*$
Но, как я сказал выше, зависит от нюансов.
Читайте также: