Excel: как удалить определенный текст из ячеек
Вы можете использовать следующие основные формулы для удаления определенного текста из ячеек в Excel:
Способ 1: удалить один конкретный текст
Эта конкретная формула удаляет «text1» из ячейки A1 .
Способ 2: удалить несколько определенных текстов
Эта конкретная формула удаляет «текст1», «текст2» и «текст3» из ячейки A1 .
В следующих примерах показано, как использовать каждый метод на практике.
Пример 1: удалить один конкретный текст
Предположим, у нас есть следующий столбец в Excel, в котором показаны позиции 15 разных баскетболистов:
Мы можем использовать следующую формулу, чтобы удалить «r» из каждого имени позиции:
Мы можем ввести эту формулу в ячейку B2 , а затем скопировать и вставить ее в каждую оставшуюся ячейку в столбце B:
Обратите внимание, что буква «r» была удалена из всех названий позиций.
Пример 2. Удаление нескольких определенных текстов
Предположим, у нас есть следующий столбец в Excel, который показывает имя пользователя для 10 разных людей на каком-то веб-сайте:
Предположим, мы хотим удалить следующий конкретный текст из каждого имени пользователя:
- тире ( — )
- восклицательные знаки ( ! )
- число 5 ( 5 )
Для этого мы можем использовать следующую формулу:
Мы можем ввести эту формулу в ячейку B2 , а затем скопировать и вставить ее в каждую оставшуюся ячейку в столбце B:
Обратите внимание, что все «-», «!» и «5» были удалены из каждого имени пользователя.
Дополнительные ресурсы
В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:
Как удалить все до или после определенного символа в Excel
Люди, которые только начинают работать в Excel часто встречаются с таким вопросом.
Допустим, у нас есть такая табличка:
Примерно так выглядит удаление всех символов после «,».
Это можно сделать разными способами. Мы рассмотрим несколько.
С помощью функции «Найти и заменить»
Это, наверное, самый быстрый и удобный способ.
Допустим, у нас та же табличка и задача:
- Копируем и вставляем столбик А в В;
- Выделите столбик и щелкните «Главная»;
- Далее — «Найти и выделить» -> «Заменить…»;
- В первом параметре укажите «,*»;
- Второй параметр не меняйте;
- Щелкните «Заменить все».
Готово! Вот результат:
Как это работает?
Символ * означает неопределенное количество символов.
Так как мы используем «,*», то это значит, что программе нужно заменить запятую и все символы после неё на пустое место.
Это будет работать только если в каждой ячейке у вас одна запятая, если же у вас не одна, то первая и все остальные данные будут заменены на пустое место.
С помощью формул
Также, мы можем выполнить нашу задачу и с помощью формул.
Допустим, у нас есть такая табличка:
Формула принимает такой вид:
Функция НАЙТИ возвращает порядковый номер запятой.
Это простой пример, давайте рассмотрим кое-что посложнее.
Теперь у нас такая табличка:
Формула, для этого примера, принимает такой вид:
Итак, также как в прошлый раз — не получится. Так как НАЙТИ будет возвращать порядковый номер первой запятой, а нам надо найти его для второй.
Мы используем небольшую хитрость, а если конкретнее, то заменяем вторую запятую на восклицательный знак, а затем с ним уже проводим операции.
И все бы хорошо, только в этом примере в каждой строке у нас ровно 2 запятые. А что делать если их неопределенное количество? Ведь в больших данных вы не будете выверять сколько запятой в каждой строке.
Итак, нам нужно найти порядковый номер последней запятой, а после уже проводить с ней операции.
Для этого примера, формула принимает такой вид:
Итак, функция ДЛСТР сначала находит количество символов в строчке с запятыми, а потом без них.
А после вычитает из первого — второе. Таким образом мы получаем количество запятых в строчке.
А затем мы заменяем последнюю на восклицательный знак.
Вот так вот можно заменять все после определенного символа с помощью формул. Конечно, с небольшими хитростями.
Плюс этого метода в том, что данные будут динамичны. То есть если что-то поменяется в изначальных данных, все поменяется и в данных после обработки.
С помощью функции «Заполнить»
Функция «Заполнить», это довольно давний инструмент. Он может помочь нам и в этом случае.
Как он работает?
Очень просто — вы просто делаете что угодно и после используете функцию. Она пытается понять логику ваших действий и продолжить её.
Давайте рассмотрим пример.
Допустим, у нас есть та же табличка:
- В первую ячейку столбика В введите то, что должно получиться после обработки;
- В следующую ячейку, то же самое;
- А теперь выделите столбик;
- И щелкните на «Главная» -> «Заполнить» -> «Мгновенное заполнение»;
Готово! Вот результат:
Эту функцию, естественно, можно использовать не только для удаления текста после символа. Она работает там, где есть логика.
Однако, иногда, она может ошибаться. Поэтому всегда проверяйте то, что получилось после обработки.
С помощью Visual Basic
И, как обычно, разберем вариант с помощью Visual Basic.
Мы создадим свою собственную функцию и будем использовать её для обработки данных.
Это крайне удобно, если вы делаете что-либо очень часто. Например, как в нашем случае, удаляете данные после символа.
Код Visual Basic:
Код, чтобы он работал, нужно вставить в Visual Basic -> «Insert» -> «Module».
Давайте рассмотрим пример её использования.
Допустим, у нас есть такая табличка. Формула принимает такой вид:
В нашей функции, первым аргументом мы указали диапазон для поиска, а вторым символ, последнюю позицию которого нам нужно найти.
С помощью Visual Basic все проще.
Вот и все! Если вам нужно сделать что-то подобное 1-2 раза, то лучше всего использовать функцию «Найти и заменить…», а если вы делаете это постоянно, то используйте Visual Basic.
Зачистка текста
Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:
- лишние пробелы перед, после или между словами (для красоты!)
- ненужные символы («г.» перед названием города)
- невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или «кривой» выгрузки из 1С, переносы строк, табуляция)
- апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)
Давайте рассмотрим способы избавления от такого «мусора».
Замена
«Старый, но не устаревший» трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.
Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша – замени на Петя», но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов:
Только не забудьте предварительно выделить нужный диапазон ячеек, иначе замена произойдет на всем листе!
Удаление пробелов
Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).
Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:
Удаление непечатаемых символов
В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».
Вариантов решения два:
- Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
- Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.
Функция ПОДСТАВИТЬ
Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:
- Текст в котором производим замену
- Старый текст – тот, который заменяем
- Новый текст – тот, на который заменяем
С ее помощью можно легко избавиться от ошибок (замена «а» на «о»), лишних пробелов (замена их на пустую строку «»), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):
Удаление апострофов в начале ячеек
Апостроф (‘) в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.
Английские буквы вместо русских
Это уже, как правило, чисто человеческий фактор. При вводе текстовых данных в ячейку вместо русских букв случайно вводятся похожие английские («це» вместо русской «эс», «игрек» вместо русской «у» и т.д.) Причем снаружи все прилично, ибо начертание у этих символов иногда абсолютно одинаковое, но Excel воспринимает их, конечно же, как разные значения и выдает ошибки в формулах, дубликаты в фильтрах и т.д.
Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:
Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу 🙂
Как удалить часть текста из ячейки в excel
В этой статье представлены различные методы удаления текста до или после определенного символа или n-го символа вхождения из ячеек в Excel.
Удаление текста до или после определенного символа с помощью функции «Найти и заменить» в Excel
Чтобы удалить весь текст до или после определенного символа с помощью функции «Найти и заменить», сделайте следующее.
1. Выберите ячейки, в которых вы хотите удалить текст до или после определенного символа, нажмите Ctrl + H , чтобы открыть Найти и заменить Диалог.
В диалоговом окне «Найти и заменить»:
1. Чтобы удалить все перед конкретным символом, например запятой, введите *, в Найти то, что текстовое окно;
2. Чтобы удалить все после определенного символа, например запятой, введите ,* в Найти то, что текстовое окно;
Ноты:
1. Вы можете изменить запятую на любой символ по своему усмотрению.
2. В этом случае из выбранных ячеек будет удален весь текст до последней запятой или после первой запятой.
2. Держать Заменить пустое текстовое поле, а затем щелкните значок Заменить все кнопка. Смотрите скриншот:
Легко удаляйте все числовые, нечисловые или указанные символы из ячеек в Excel
Kutools для Excel‘s Удалить персонажа Утилита поможет вам легко удалить все числовые, нечисловые или указанные символы из выбранных ячеек в Excel.
Скачать полную версию 30 — дневная бесплатная трасса Kutools for Excel сейчас!
Удалять тексты до или после первого / последнего конкретного символа по формуле
В этом разделе будут показаны формулы удаления всего до или после первого / последнего конкретного символа из ячеек в Excel.
Чтобы удалить все до первой запятой, пожалуйста:
Выберите пустую ячейку, скопируйте и вставьте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите Ручка заполнения чтобы применить формулу к другим ячейкам. Смотрите скриншот:
Формула: Удалите все до первой запятой
= ВПРАВО ( B5 , LEN ( B5 )-НАХОДИТЬ(» , «, B5 ))
Ноты:
1. В приведенной выше формуле B5 — это ячейка, из которой вы удаляете текст, а «,» — это символ, на основе которого вы удаляете тексты.
2. Чтобы удалить все символы перед последним конкретным символом, используйте следующую формулу:
= ВПРАВО ( B5 , LEN ( B5 ) -НАЙТИ («@», ПОДСТАВИТЬ ( B5 ,» Характер «,» @ «, (LEN ( B5 ) -ЛЕН (ЗАМЕНА ( B5 ,» Характер «,» «))) / LEN (» Характер «))))
Удалить все после первой запятой, пожалуйста:
Выберите пустую ячейку, скопируйте и вставьте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите Ручка заполнения чтобы применить формулу к другим ячейкам. Смотрите скриншот:
Формула: Удалите все после первой запятой
= ЛЕВЫЙ (B5; НАЙТИ («;»; B5) -1)
Ноты:
1. В приведенной выше формуле B5 — это ячейка, из которой вы удаляете текст, а «,» — это символ, на основе которого вы удаляете тексты.
2. Чтобы удалить все после последнего конкретного символа, используйте эту формулу:
= ВЛЕВО ( B5 , НАЙТИ («@», ПОДСТАВИТЬ ( B5 ,» персонаж «,» @ «, LEN ( B5 ) -ЛЕН (ЗАМЕНА ( B5 ,» персонаж «,» «)))) — 1)
Удалять тексты до или после n-го символа вхождения по формуле
Приведенные ниже формулы могут помочь удалить все до или после n-го символа вхождения из ячеек в Excel.
Чтобы удалить все символы перед n-м вхождением из ячеек, тебе нужно:
Выберите пустую ячейку для вывода результата, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите Ручка заполнения чтобы применить формулу к другим ячейкам. Смотрите скриншот:
Формула: Удалите все до второй запятой
= ПРАВИЛЬНО (ЗАМЕНИТЬ ( B5 » , «, СИМВОЛ (9), 2 ), LEN ( B5 ) — НАЙТИ (СИМВОЛ (9), ЗАМЕНИТЬ ( B5 » , «, СИМВОЛ (9), 2 ), 1) + 1)
Ноты:
1. В формулах B5«,» а также 2 число означает, что все содержимое после второй запятой будет удалено из ячейки B5.
2. Вы можете изменить «,» а также 2 номер на любой символ и номер позиции вхождения по мере необходимости.
Чтобы удалить все после n-го символа вхождения из ячеек, тебе нужно:
Выберите пустую ячейку для вывода результата, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите Ручка заполнения чтобы применить формулу к другим ячейкам. Смотрите скриншот:
Формула: Удалить все после второй запятой
= ЛЕВЫЙ (ПОДСТАВИТЬ ( B5 ,» , «, СИМВОЛ (9), 2 ), НАЙТИ (СИМВОЛ (9), ЗАМЕНИТЬ ( B5 ,» , «, СИМВОЛ (9), 2 ), 1) -1)
Ноты:
1. В формулах B5«,» а также 2 число означает, что все содержимое после второй запятой будет удалено из ячейки A7.
2. Вы можете изменить «,» а также 2 номер на любой символ и номер позиции вхождения по мере необходимости.
Легко удаляйте тексты до/после определенного символа с помощью Kutools for Excel
Если для каждой ячейки в диапазоне есть только один разделитель запятой, и вы хотите удалить все до или после этой запятой из ячеек, попробуйте Разделить клетки полезности Kutools for Excel. Эта утилита поможет вам решить проблему всего в несколько кликов:
1. Выберите ячейки, из которых вы удалите все до или после запятой, а затем щелкните Kutools > Текст > Разделить клетки. Смотрите скриншот:
2. в Разделить клетки диалоговом окне выберите Разделить на столбцы вариант в Тип раздел, а в Разделить на раздел, выберите Другое вариант и введите запятую в пустое поле, а затем щелкните OK кнопка. Смотрите скриншот:
3. Еще один Разделить клетки появится диалоговое окно, выберите пустую ячейку для поиска текстов и нажмите кнопку OK кнопку.
Затем вы можете увидеть, что выбранные ячейки разделены определенным символом — запятой. Смотрите скриншот:
Если вы хотите получить бесплатную пробную версию ( 30 -день) этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.