Удалить последнее слово в ячейке excel
Иногда при работе с Excel возникает необходимость удалить первое слово или же несколько слов в начале фраз в ячейке.
Для решения задачи вам потребуется объединить несколько функций программы.
Задача удаления первого слова является частным случаем более общей (см. статью):
Удалить первое слово из ячейки: составляем формулу
Технически, первое слово — это все символы в ячейке до первого пробела или до конца строки, если слово в ячейке всего одно. Как его удалить?
Находим позицию первого пробела
Есть 2 варианта формул, оба требуют сначала найти позицию первого символа пробела в строке, это делается с помощью функции ПОИСК или функции НАЙТИ. Обе ищут позицию в ячейке первого вхождения значения, указанного в первом аргументе, просматривая ячейку (второй аргумент) слева направо.
А что, если слово в строке одно, и пробела там нет? Обе функции вернут ошибку. Но ее можно обойти, производя поиск не в самой ячейке, а в тексте ячейки с присоединенным к нему справа пробелом (&» «). Смотрите пример:
Высчитываем позицию первого пробела в строке.
Может быть и другая неприятная ситуация — в начале ячейке может быть лишний пробел, что может нам испортить решение задачи. Если есть такая вероятность, рекомендуется сначала удалить лишние пробелы.
Заменяем фрагмент до первого пробела на пустоту
Теперь, когда нам известна позиция первого символа идущего после слова, ею можно воспользоваться, чтобы заменить слово на пустоту, что эквивалентно его удалению. Пустота обозначается кавычками, между которыми ничего нет («»).
Нам потребуется функция «ЗАМЕНИТЬ». Ей мы указываем ячейку, в которой производится замена, начиная с какой позиции, сколько символов, и на что заменим этот промежуток. Обратите внимание — функция не выдает ошибку в последней строке, несмотря на то, что в ячейке A8 меньше символов, чем число, которое мы ей указываем в B4.
Заменяем часть ячейки до первого пробела на пустоту
Чтобы не создавать два столбца, можно совместить формулы в одну составную.
Просто заменим в формулах третьего столбца ссылки на формулы второго столбца самими этими формулами. Так будет выглядеть итоговая составная формула удаления первого слова в ячейке для первой строки с данными:
А ниже процесс, как происходит ее создание:
Удалить первое слово в ячейках в 2 клика
Как видно, формулы выше не очень просты, и требуется время, даже чтобы просто их написать. Помимо этого, формулы требуют создания дополнительного столбца (или двух). Надстройка !SEMTools поможет упростить и ускорить решение задачи. Ниже показан пример удаления первых слов в ячейках, прямо на месте:
В примере ниже удаляем уже 2 слова в начале всех ячеек столбца:
Аналогично решается задача для трех и более слов — можно использовать пункт «Первые N», чтобы указать количество удаляемых слов самостоятельно.
Теперь нет необходимости прописывать сложные формулы в Excel.
!SEMTools поможет автоматизировать процессы и решит ваши задачи буквально за пару кликов!
Довольно часто в Excel нужно убрать из строки символы до определенного знака. Это может быть как один символ (пробел, точка, запятая, двоеточие и т.д.), так и символ из определенного множества (цифра, английская буква).
В зависимости от этих условий, решение может быть простым, а может потребовать использование довольно сложных формул и процедур.
В этой статье я подробно опишу известные мне варианты. Поехали!
Удалить текст до последнего символа
Наиболее просто удалить текст в диапазоне ячеек до последнего вхождения заданного символа или текста. Можно воспользоваться процедурой «Найти и заменить»:
- Выделить диапазон
- Вызвать процедуру поиска и замены со вкладки «Главная» или сочетанием клавиш Ctrl+H
- использовать подстановочный символ * (звездочку) прямо перед искомым в строке поиска
- оставить пустым второе поле и нажать ОК
Процедура заменит все символы до знака на пустоту, иначе говоря, удалит их. Звездочка как раз и обозначает «все символы».
Удалить текст до первого пробела — формула
Данная формула универсальна и позволит удалить не только текст до первого пробела, но и до любого другого первого вхождения символа в строке.
Однако удалить первое слово в ячейке — самая популярная задача, и пробел как раз и является символом, который отгораживает первое слово слева от остального текста.
Результат применения формулы
Формулы для других символов аналогичны:
Убрать текст в ячейке до первой запятой:
А чтобы удалить текст до вхождения последовательности символов, например, слова, нужно использовать в формуле длину этой последовательности, сократив ее на 1:
Здесь из адресов удаляется текст до номера дома. Длина текста в кавычках — 3 символа (д, точка и пробел):
Удалить текст в ячейке до определенного слова
Удалить текст до первой цифры
Пожалуй, вы уже догадались, но я все же объясню, как работают формулы выше:
-
ищет позицию первого вхождения текстового паттерна (он всегда в кавычках). Соответствующий фрагмент выделен красным. принимает эту позицию как аргумент;
- она же заменяет фрагмент от первого символа до этой позиции на пустоту (две кавычки подряд), таким образом, удаляя его.
Следовательно, чтобы удалить текст до первой цифры, нужно вычислить позицию первой цифры в строке. Поскольку цифр не одна, а 10, простая формула уже не подойдет. Но можно сделать это вот такой формулой массива:
А подставив это выражение в исходную формулу, мы сможем решить исходную задачу — получить текст после первой цифры включительно, удалив текст до нее.
Что за числа 48 и 57, можно прочитать подробнее тут:
Обратите внимание на фигурные скобки! Их не нужно вводить, они появятся сами при вводе формулы сочетанием клавиш Ctrl+Shift+Enter.
Формула выдаст ошибку, если цифр в строке нет.
Убираем текст до первой цифры в ячейках формулами массива
Удаление текста в 1-2 клика с !SEMTools
Каждый из перечисленных способов хорош по-своему, но всем им недостает одного важного фактора — скорости исполнения.
В каждом из случаев нужно вводить много символов и производить много действий.
Для экономии времени я включил быстрые процедуры в меню своей надстройки для Excel — !SEMTools.
Теперь удалить текст до первого или последнего вхождения конкретного символа или подстроки, включая и не включая сам текст, можно в считанные мгновения!
При этом можно как менять данные на месте, так и автоматически выводить в соседний столбец — за это отвечает глобальный чекбокс.
Хотите так же быстро обращаться с текстовыми данными в Excel и испытать на практике еще более 500 похожих процедур?
Жмите на кнопку ниже, чтобы бесплатно скачать и подключить пробную версию надстройки !SEMTools. Процедура займет пару минут.
Иногда при работе с 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 таких же простых и быстрых функций.
Простая, на первый взгляд, задача с не очевидным решением: извлечь из строки текста последнее слово. Ну или, в общем случае, последний фрагмент, отделенный заданным символом-разделителем (пробелом, запятой и т.д.) Другими словами, необходимо реализовать реверсивный поиск (от конца к началу) в строке заданного символа и извлечь потом все символы справа от него.
Давайте рассмотрим традиционно несколько способов решения на выбор: формулами, макросами и через Power Query.
Способ 1. Формулы
Чтобы проще было понять суть и механику формулы, начнем немного издалека. Сначала увеличим количество пробелов между словами в нашем исходном тексте до, например 20 штук. Сделать это можно при помощи функции замены ПОДСТАВИТЬ (SUBSTITUTE) и функции повтора заданного символа N-раз - ПОВТОР (REPT) :
Теперь отрежем от конца получившегося текста 20 символов с помощью функции ПРАВСИМВ (RIGHT) :
Уже теплее, да? Осталось убрать лишние пробелы с помощью функции СЖПРОБЕЛЫ (TRIM) и задача будет решена:
В английской версии наша формула будет выглядеть, соответственно:
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";20));20))
Надеюсь, понятно, что в принципе не обязательно вставлять именно 20 пробелов - подойдет любое количество, лишь бы оно было больше, чем длина самого длинного слова в исходном тексте.
Способ 2. Макрофункция
Задачу извлечения последнего слова или фрагмента из текста также можно решить с помощью макросов, а именно - написать функцию реверсивного поиска в Visual Basic, которая будет делать то, что нам нужно - искать заданную подстроку в строке в обратном направлении - от конца к началу.
Нажмите сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор макросов. Затем добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Теперь можно сохранить книгу (в формате с поддержкой макросов!) и воспользоваться созданной функцией в следующем синтаксисе:
=LastWord(txt ; delim ; n)
- txt - ячейка с исходным текстом
- delim - символ-разделитель (по умолчанию - пробел)
- n - какое по счету слово с конца необходимо извлечь (по умолчанию - первое с конца)
При любых изменениях в исходном тексте в будущем наша макрофункция будет "на лету" пересчитываться, как и любая стандартная функция листа 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):
В открывшемся окне редактора запросов на вкладке Преобразование (Transform) выберем команду Разделить столбец - По разделителю (Split Column - By delimiter) и затем останется задать символ-разделитель и выбрать опцию Самый правый разделитель, чтобы разрубить не все слова, а только последнее:
После нажатия на ОК последнее слово будет отделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув по его заголовку правой кнопкой мыши и выбрав Удалить (Delete ) . Также можно переименовать оставшийся столбец в шапке таблицы.
Результаты можно выгрузить обратно на лист, используя команду Главная - Закрыть и загрузить - Закрыть и загрузить в . (Home - Close & Load - Close & Load to. ) :
И в итоге получаем:
Вот так - дешево и сердито, без формул и макросов, почти не касаясь клавиатуры :)
Если в будущем исходный список изменится, то достаточно будет правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 обновить наш запрос.
Простая, на первый взгляд, задача с не очевидным решением: извлечь из строки текста последнее слово. Ну или, в общем случае, последний фрагмент, отделенный заданным символом-разделителем (пробелом, запятой и т.д.) Другими словами, необходимо реализовать реверсивный поиск (от конца к началу) в строке заданного символа и извлечь потом все символы справа от него.
Давайте рассмотрим традиционно несколько способов решения на выбор: формулами, макросами и через Power Query.
Способ 1. Формулы
Чтобы проще было понять суть и механику формулы, начнем немного издалека. Сначала увеличим количество пробелов между словами в нашем исходном тексте до, например 20 штук. Сделать это можно при помощи функции замены ПОДСТАВИТЬ (SUBSTITUTE) и функции повтора заданного символа N-раз - ПОВТОР (REPT) :
Теперь отрежем от конца получившегося текста 20 символов с помощью функции ПРАВСИМВ (RIGHT) :
Уже теплее, да? Осталось убрать лишние пробелы с помощью функции СЖПРОБЕЛЫ (TRIM) и задача будет решена:
В английской версии наша формула будет выглядеть, соответственно:
=TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";20));20))
Надеюсь, понятно, что в принципе не обязательно вставлять именно 20 пробелов - подойдет любое количество, лишь бы оно было больше, чем длина самого длинного слова в исходном тексте.
Способ 2. Макрофункция
Задачу извлечения последнего слова или фрагмента из текста также можно решить с помощью макросов, а именно - написать функцию реверсивного поиска в Visual Basic, которая будет делать то, что нам нужно - искать заданную подстроку в строке в обратном направлении - от конца к началу.
Нажмите сочетание клавиш Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор макросов. Затем добавьте новый модуль через меню Insert - Module и скопируйте туда следующий код:
Теперь можно сохранить книгу (в формате с поддержкой макросов!) и воспользоваться созданной функцией в следующем синтаксисе:
=LastWord(txt ; delim ; n)
- txt - ячейка с исходным текстом
- delim - символ-разделитель (по умолчанию - пробел)
- n - какое по счету слово с конца необходимо извлечь (по умолчанию - первое с конца)
При любых изменениях в исходном тексте в будущем наша макрофункция будет "на лету" пересчитываться, как и любая стандартная функция листа 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):
В открывшемся окне редактора запросов на вкладке Преобразование (Transform) выберем команду Разделить столбец - По разделителю (Split Column - By delimiter) и затем останется задать символ-разделитель и выбрать опцию Самый правый разделитель, чтобы разрубить не все слова, а только последнее:
После нажатия на ОК последнее слово будет отделено в новый столбец. Ненужный первый столбец можно удалить, щелкнув по его заголовку правой кнопкой мыши и выбрав Удалить (Delete ) . Также можно переименовать оставшийся столбец в шапке таблицы.
Результаты можно выгрузить обратно на лист, используя команду Главная - Закрыть и загрузить - Закрыть и загрузить в . (Home - Close & Load - Close & Load to. ) :
И в итоге получаем:
Вот так - дешево и сердито, без формул и макросов, почти не касаясь клавиатуры :)
Если в будущем исходный список изменится, то достаточно будет правой кнопкой мыши или сочетанием клавиш Ctrl + Alt + F5 обновить наш запрос.
Читайте также: