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

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

  • автор:

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Последнее слово

Простая, на первый взгляд, задача с не очевидным решением: извлечь из строки текста последнее слово. Ну или, в общем случае, последний фрагмент, отделенный заданным символом-разделителем (пробелом, запятой и т.д.) Другими словами, необходимо реализовать реверсивный поиск (от конца к началу) в строке заданного символа и извлечь потом все символы справа от него. Давайте рассмотрим традиционно несколько способов решения на выбор: формулами, макросами и через Power Query.

Способ 1. Формулы

Чтобы проще было понять суть и механику формулы, начнем немного издалека. Сначала увеличим количество пробелов между словами в нашем исходном тексте до, например 20 штук. Сделать это можно при помощи функции замены ПОДСТАВИТЬ (SUBSTITUTE) и функции повтора заданного символа N-раз — ПОВТОР (REPT) : Добавляем пробелы между словами
Теперь отрежем от конца получившегося текста 20 символов с помощью функции ПРАВСИМВ (RIGHT) : Берем последние 20 знаков
Уже теплее, да? Осталось убрать лишние пробелы с помощью функции СЖПРОБЕЛЫ (TRIM) и задача будет решена: Убираем лишние пробелыВ английской версии наша формула будет выглядеть, соответственно:
=TRIM(RIGHT(SUBSTITUTE(A1;» «;REPT(» «;20));20))
Надеюсь, понятно, что в принципе не обязательно вставлять именно 20 пробелов — подойдет любое количество, лишь бы оно было больше, чем длина самого длинного слова в исходном тексте.
И если исходный текст нужно разделить не по пробелу, а по другому символу-разделителю (например, по запятой), то нашу формулу надо будет чуть-чуть подправить: Если вместо пробела запятая

Способ 2. Макрофункция

Задачу извлечения последнего слова или фрагмента из текста также можно решить с помощью макросов, а именно — написать функцию реверсивного поиска в Visual Basic, которая будет делать то, что нам нужно — искать заданную подстроку в строке в обратном направлении — от конца к началу. Нажмите сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор макросов. Затем добавьте новый модуль через меню Insert — Module и скопируйте туда следующий код:

Function LastWord(txt As String, Optional delim As String = " ", Optional n As Integer = 1) As String arFragments = Split(txt, delim) LastWord = arFragments(UBound(arFragments) - n + 1) End Function
  • txt — ячейка с исходным текстом
  • delim — символ-разделитель (по умолчанию — пробел)
  • n — какое по счету слово с конца необходимо извлечь (по умолчанию — первое с конца)

Применение функции LastWord

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

Способ 3. Power Query

Power Query — это бесплатная надстройка от Microsoft для импорта данных в Excel из практически любых источников и последующей трансформации загруженных данных в любой вид. Мощь и крутизна этой надстройки настолько велики, что Microsoft встроила все ее возможности в Excel 2016 по умолчанию. Для Excel 2010-2013 Power Query можно бесплатно скачать отсюда.

Наша задача по отделению последнего слова или фрагмента через заданный разделитель с помощью Power Query решается очень легко.

Сначала превратим нашу таблицу с данными в умную с помощью сочтания клавиш Ctrl + T или команды Главная — Форматировать как таблицу (Home — Format as Table) :

Форматировать как таблицу

Затем загрузим созданную «умную таблицу» в Power Query с помощью команды Из таблицы / диапазона (From table/range) на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013):

Загружаем таблицу в Power Query

В открывшемся окне редактора запросов на вкладке Преобразование (Transform) выберем команду Разделить столбец — По разделителю (Split Column — By delimiter) и затем останется задать символ-разделитель и выбрать опцию Самый правый разделитель, чтобы разрубить не все слова, а только последнее:

Делим по правому разделителю

После нажатия на ОК последнее слово будет отделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув по его заголовку правой кнопкой мыши и выбрав Удалить (Delete ) . Также можно переименовать оставшийся столбец в шапке таблицы.

Результаты можно выгрузить обратно на лист, используя команду Главная — Закрыть и загрузить — Закрыть и загрузить в . (Home — Close & Load — Close & Load to. ) :

Выгружаем результаты на лист

И в итоге получаем:

Последнее слово из текста

Вот так — дешево и сердито, без формул и макросов, почти не касаясь клавиатуры 🙂

Если в будущем исходный список изменится, то достаточно будет правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 обновить наш запрос.

Ссылки по теме

  • Разделение слипшегося текста по столбцам
  • Анализ и разбор текста регулярными выражениями
  • Извлечение первых слов из текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Как в ячейке Excel удалить символы справа

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

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

В показанном выше примере в ячейке C5 приведена формула:

=ЗНАЧЕН(ЛЕВСИМВ( B5 ;ДЛСТР( B5 )- 5 ))

которая отсекает «евро» от каждого значения, возвращая только число.

Пояснение

Функция ЛЕВСИМВ идеально подходит для извлечения символов, начинающихся с левой стороны текстовой строки.
Мы используем ЛЕВСИМВ в этой формуле, чтобы извлечь все символы до количества символов, которое мы хотим извлечь.
Проблема для значений переменной длины состоит в том, что мы не знаем точно, сколько символов нужно извлечь.
Вот где используется функция ДЛСТР.
ДЛСТР вычисляет общую длину строки. Для ячейки B5 (5 евро) общая длина равна 6 символов. Чтобы получить количество извлекаемых символов, мы вычитаем 5, что составляет длину » евро», включая пробел. Результатом будет 1, которые передаются в ЛЕВСИМВ как количество символов для извлечения. ЛЕВСИМВ затем возвращает текст «5» как текстовое значение.

Наконец, поскольку нам нужно числовое значение (а не текст), мы пропускаем текст через функцию ЗНАЧЕН, которая преобразует числа в текстовом формате в фактические числа.
Шаги формулы выглядят так:

Копировать, убрать/удалить первое слово или несколько первых слов в ячейке Excel

Бывает, что возникает потребность в удалении первого слова или нескольких слов вначале фразы в ячейке таблицы «Эксель». Выполнить это руками очень сложно, особенно, когда список представляет собой перечень из нескольких сотен или тысяч позиций, при помощи одной функции это сделать тоже невозможно.

Чтобы выполнить задачу по удалению первых 2-3 (или первого одного) слов необходимо воспользоваться системой (сочетанием) из нескольких функций, о которых расскажем ниже:

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

Вторая функция «НАЙТИ» — находит позицию текста и присваивает позиции числовое значение. Например, если замененный нами на символ пробел будет стоять на 12 месте от начала строки, то функция найти примет значение 12.

Третья функция это «ЛЕВСИМВ» или «ПРАВСИМВ» (в зависимости от того будем ли мы удалять слова справа или слева). «ЛЕВСИМВ» или «ПРАВСИМВ» — присваивают ячейке строго определенное количество символов из текста.

Четвертая функция «ДЛСТР» — находит длину строки в ячейке и определяет количество символов в выбранной ячейке.

Удалить лишние слова

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

Система выглядит следующим образом:

=ПРАВСИМВ(B3;((ДЛСТР(B3))-(НАЙТИ(«*»;ПОДСТАВИТЬ(B3;» «;»*»;3)))))

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

копировать слова

Чтобы скопировать первые несколько слов можно воспользоваться похожей системой, только не удалять лишние знаки а наоборот оставить первые несколько знаков относящиеся к «нужным» словам.

Функции выглядят следующим образом:

=ЛЕВСИМВ(B3; НАЙТИ(«*»;ПОДСТАВИТЬ(B3;» «;»*»;3)).

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

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