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

Обновлено: 23.11.2024

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

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

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

В этом уроке мы покажем вам 3 способа сделать это:

Использование поиска и замены
Использование формулы
Использование VBA

Мы также покажем вам, как удалить текст после n-го экземпляра определенного символа (например, после второго дефиса или третьей запятой).

Оглавление

Удаление текста после определенного символа

Предположим, у нас есть следующий набор данных адресов электронной почты, и мы хотим удалить все после символа @ в каждой ячейке:

Есть три способа сделать это. Давайте рассмотрим каждый метод один за другим.

Способ 1. Использование поиска и замены для удаления текста после определенного символа

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

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

Одной из замечательных особенностей этого диалогового окна является то, что оно позволяет использовать подстановочные знаки, чтобы помочь вам найти то, что вам нужно. Например, вы можете использовать подстановочный знак вопросительного знака (?) в качестве заполнителя для одного символа в строке поиска.

Поэтому, если вы введете 'b?t' в качестве строки поиска, будут найдены слова bat, bit, бот и т. д. (если они существуют на вашем листе).

В нашем примере мы будем использовать подстановочный знак звездочки (*). Этот подстановочный знак позволяет указать местозаполнитель для любого количества символов в строке поиска.

Поэтому, если вы введете '@*' в качестве строки поиска, функция Найти и заменить найдет в каждой ячейке все, что находится после символа '@' включительно.

Давайте рассмотрим шаг за шагом, как можно использовать Найти и заменить для удаления текста после символа @:

Выберите диапазон ячеек, с которыми вы хотите работать.
Нажмите Найти и выбрать на вкладке Главная (в группе Редактирование)
Появится раскрывающееся меню. Выберите Заменить.
Откроется диалоговое окно Найти и заменить. Введите «@*» в текстовое поле рядом с «Найти что».
Оставьте текстовое поле рядом с «Заменить на» пустым. Это потому, что вы хотите удалить все после символа @ в каждой ячейке.
Нажмите кнопку "Заменить все".

Это заменит весь текст после символа @ во всех ячейках.

Метод 2. Использование формулы для удаления текста после определенного символа

Во втором методе используется формула, объединяющая функции Excel в ЛЕС и НАЙТИ.

Функция ВЛЕВО используется для извлечения заданного количества символов из текста, начиная с самого левого символа. Его синтаксис:

текст – это строка, из которой вы хотите извлечь символы.
num_of_characters – это количество символов, которое нужно извлечь из текста, начиная слева.

Функция НАЙТИ используется для поиска индекса или местоположения символа или подстроки внутри текста. Его синтаксис:

текст — это текст, который вы хотите найти. Это может быть строка или ссылка на ячейку.
search_string – это символ или подстрока, для которой вы хотите найти индекс в тексте.

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

Давайте рассмотрим шаг за шагом, как можно использовать эту формулу для удаления текста после символа @ в нашем примере:

Выберите первую ячейку столбца, в которой должны отображаться результаты. В нашем примере это будет ячейка B2.
Введите формулу: =ЛЕВЫЙ(A2,НАЙТИ("@",A2)-1).
Нажмите клавишу возврата.
Это даст вам текст, полученный после удаления всего, что следует за символом @, из строки в ячейке A2.
Дважды щелкните маркер заполнения (в нижнем левом углу) ячейки B2. Это скопирует формулу во все остальные ячейки столбца B. Вы также можете перетащить маркер заполнения вниз, чтобы добиться того же эффекта. Вот исходный и преобразованный столбцы рядом:
Если вы хотите сохранить только преобразованные версии текста, выберите эти ячейки (B2:B11), скопируйте их и вставьте туда же, где и значения.
После этого при необходимости вы можете удалить столбец A.

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

Чтобы понять, как работает эта формула, нам нужно разбить ее на части:

Сначала мы использовали функцию НАЙТИ, чтобы найти позицию символа @ в тексте: НАЙТИ("@", A2).
Нам нужны все символы до (и не включая) символа «@». Итак, мы вычли 1 из этого индекса: НАЙТИ("@", A2)-1. Это дает индекс последнего символа перед символом «@».
Наконец, мы использовали функцию ЛЕВЫЙ, чтобы извлечь только символы, начинающиеся слева до этого индекса: ЛЕВЫЙ(A2,НАЙТИ("@",A2)-1)

Таким образом, мы получаем все символы в A2 от самого левого символа до символа перед символом @!

Способ 3. Использование VBA для удаления текста после определенного символа

Наконец, если вы хорошо разбираетесь в VBA и хотите быстро выполнять свою работу с помощью макроса, это отличный способ для вас.

Вот код, который мы будем использовать. Вы можете выбрать и скопировать его:

Выполните следующие действия:

На ленте меню Разработчик выберите Visual Basic.
Когда откроется окно VBA, нажмите Вставить->Модуль. Теперь можно приступать к кодированию. Введите или скопируйте и вставьте приведенные выше строки кода в окно модуля. Теперь ваш код готов к запуску.
Выделите диапазон ячеек, содержащих текст, который вы хотите преобразовать. Убедитесь, что столбец рядом с ним пуст, потому что именно в нем макрос будет отображать результаты.
Перейдите к Разработчик->Макросы->remove_text_after_char->Выполнить.
Теперь вы увидите усеченные строки рядом с выбранным диапазоном ячеек.

Примечание. Вы можете изменить символ в строке 6 с «@» на нужный вам символ.

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

Удаление текста после n-го экземпляра определенного символа

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

Например, в наборе данных, показанном ниже, вы хотите удалить весь текст после второй запятой. (Возможно, вы хотите оставить только первые два элемента):

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

original_string – это текст или ссылка на ячейку, с которой вы хотите работать.
old_character — это символ, который вы хотите заменить.
new_character — это символ, которым вы хотите заменить old_character.
instance_number — это экземпляр old_character, который вы хотите заменить. Это означает, что если вы хотите заменить вторую запятую в original_string, это значение будет равно 2.

Итак, вот шаги, которые нужно выполнить, если вы хотите удалить весь текст после второй запятой в нашем наборе данных:

Выберите первую ячейку столбца, в которой должны отображаться результаты. В нашем примере это будет ячейка B2.
Введите формулу:
Нажмите клавишу возврата.
Это даст вам текст, полученный после удаления всего, что следует за второй запятой, из строки в ячейке A2.
Дважды щелкните маркер заполнения (в нижнем левом углу) ячейки B2. Это скопирует формулу во все остальные ячейки столбца B.Вы также можете перетащить маркер заполнения вниз, чтобы добиться того же эффекта. Вот исходный и преобразованный столбцы рядом:
Если вы хотите сохранить только преобразованные версии текста, выберите эти ячейки (B2:B5), скопируйте их и вставьте в то же место, что и значения.
После этого при необходимости вы можете удалить столбец A.

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

Чтобы понять, как работает эта формула, нам нужно разбить ее на части:

Таким образом, мы получаем все символы в A2 от самого левого символа до символа перед второй запятой!

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

Поэтому, если вы хотите удалить все после третьей запятой, ваша формула изменится на:

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

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

Мы попытались представить методы простыми и легкими шагами. Мы надеемся, что вам было легко понять и применить наши инструкции.

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

Удалить текст до, после или между двумя символами с помощью функции «Найти и заменить»

Для манипуляций с данными в нескольких ячейках лучше всего подходит инструмент «Найти и заменить». Чтобы удалить часть строки, предшествующую или следующую за определенным символом, выполните следующие действия:

Выделите все ячейки, в которых вы хотите удалить текст.
Нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить.
В поле Найти введите одну из следующих комбинаций:
- Чтобы удалить текст перед заданным символом, введите символ, перед которым стоит звездочка (*char).
- Чтобы удалить текст после определенного символа, введите символ, за которым следует звездочка (char*).
- Чтобы удалить подстроку между двумя символами, введите звездочку, окруженную двумя символами (char*char).
Оставьте поле Заменитьна пустым.
Нажмите Заменить все.

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

Чтобы удалить подстроку перед запятой, введите звездочку, запятую и пробел (*, ) в поле Найти.

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

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

Совет. Если вы предпочитаете, чтобы имена и номера телефонов были разделены запятой, введите запятую (,) в поле Заменить на.

Удалить часть текста с помощью Flash Fill

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

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

Готово!

Удалить текст с помощью формул

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

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

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

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

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

=ЛЕВО(A2, ПОИСК(",", A2) -1)

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

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

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

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

=ВПРАВО(A2, ДЛСТР(A2) - ПОИСК(",", A2))

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

=ОТРЕЗАТЬ(ПРАВО(A2, ДЛСТР(A2) - ПОИСК(",", A2)))

В обоих приведенных выше примерах предполагается, что в исходной строке есть только один экземпляр разделителя. При наличии нескольких вхождений текст будет удален до/после первого вхождения.
Функция ПОИСК не чувствительна к регистру, то есть она не делает различий между строчными и прописными буквами. Если вашим конкретным символом является буква и вы хотите различать регистр букв, используйте функцию НАЙТИ с учетом регистра вместо ПОИСК.

Как удалить текст после N-го появления символа

В ситуации, когда исходная строка содержит несколько экземпляров разделителя, вам может потребоваться удалить текст после определенного экземпляра. Для этого используйте следующую формулу:

Где n — это вхождение символа, после которого следует удалить текст.

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

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

Ключевой частью формулы является функция НАЙТИ, которая вычисляет положение n-го разделителя (в нашем случае запятой). Вот как:

Заменяем 2-ю запятую в A2 символом решетки (или любым другим символом, которого нет в ваших данных) с помощью ПОДСТАВКИ:

Это число передается непосредственно аргументу num_chars функции LEFT, запрашивая получение первых 12 символов из A2:

Как удалить текст перед N-м вхождением символа

Общая формула для удаления подстроки перед определенным символом:

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

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

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

Итак, мы выясняем, сколько символов стоит после n-го разделителя, и извлекаем справа подстроку соответствующей длины. Ниже приведена расшифровка формулы:

Во-первых, мы заменяем вторую запятую в A2 символом решетки:

Результирующая строка помещается в аргумент text функции RIGHT:

Далее нам нужно определить, сколько символов нужно извлечь из конца строки. Для этого мы находим позицию символа решетки в приведенной выше строке (равной 13):

И вычтите его из общей длины строки (равной 28):

Разница (15) переходит ко второму аргументу RIGHT, предписывая извлечь последние 15 символов из строки в первом аргументе:

Вывод представляет собой подстроку "(102) 123-4568", которая очень близка к желаемому результату, за исключением начального пробела. Итак, мы используем функцию TRIM, чтобы избавиться от него.

Как удалить текст после последнего вхождения символа

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

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

Для достижения цели вы можете удалить текст после последней запятой в A2 с помощью этой формулы:

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

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

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

Сначала мы узнаем, сколько запятых в исходной строке. Для этого заменяем каждую запятую ничем ("") и подаем полученную строку в функцию ДЛСТР:

Для A2 результат равен 35 — количеству символов в A2 без запятых.

Вычтите указанное выше число из общей длины строки (38 символов):

… и вы получите 3, что является общим количеством запятых в A2 (а также порядковым номером последней запятой).

Далее вы используете уже знакомую комбинацию функций НАЙТИ и ПОДСТАВИТЬ, чтобы получить позицию последней запятой в строке. Номер экземпляра (3-я запятая в нашем случае) предоставляется вышеупомянутой формулой LEN SUBSTITUTE:

Похоже, что 3-я запятая — это 23-й символ в A2, то есть нам нужно извлечь 22 предшествующих ей символа. Таким образом, мы помещаем приведенную выше формулу минус 1 в аргумент num_chars функции LEFT:

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

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

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

В качестве последнего штриха мы вкладываем его в функцию TRIM, чтобы убрать начальные пробелы:

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

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

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

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

Как вы видели в приведенных выше примерах, вы можете решить практически любой вариант использования, используя собственные функции Excel в различных комбинациях. Проблема в том, что вам нужно запомнить несколько хитрых формул. Хм, а что, если мы напишем собственную функцию для всех сценариев? Звучит как хорошая идея. Итак, добавьте следующий код VBA в свою книгу (подробные шаги по вставке VBA в Excel здесь):

Наша функция называется RemoveText и имеет следующий синтаксис:

Строка — исходная текстовая строка. Может быть представлен ссылкой на ячейку.

Разделитель - символ, до/после которого следует удалить текст.

Вхождение — экземпляр разделителя.

Is_after — логическое значение, указывающее, с какой стороны разделителя следует удалить текст. Может быть одиночным символом или последовательностью символов.

TRUE — удалить все после разделителя (включая сам разделитель).
FALSE — удалить все до разделителя (включая сам разделитель).

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

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

=УдалитьТекст(A3, ", ", 1, ИСТИНА)

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

=УдалитьТекст(A3, ", ", 1, ЛОЖЬ)

Поскольку наша пользовательская функция принимает строку в качестве разделителя, мы ставим запятую и пробел (",") во 2-м аргументе, чтобы впоследствии избавить себя от необходимости обрезать начальные пробелы.

Наша пользовательская функция прекрасно работает, не так ли? Но если вы думаете, что это комплексное решение, то следующего примера вы еще не видели :)

Удалить все до, после или между символами

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

Здесь мы более подробно рассмотрим функцию «Удалить по положению», расположенную на вкладке Данные Ablebits > группа Текст > Удалить .

Ниже мы рассмотрим два наиболее распространенных сценария.

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

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

Выберите параметр Все символы перед текстом или Все символы после текста и введите ключевой текст (или символ) в поле рядом с ним.
В зависимости от того, следует ли рассматривать прописные и строчные буквы как разные или как одни и те же символы, установите или снимите флажок С учетом регистра.
Нажмите Удалить.

В этом примере мы удаляем все символы, предшествующие слову "ошибка" в ячейках A2:A8:

И получить именно тот результат, который нам нужен:

Удалить текст между двумя символами

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

В качестве примера мы удаляем все, что находится между двумя символами тильды (~), и в результате получаем идеально очищенные строки:

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

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

Практика загрузки рабочей тетради

Вы можете скачать книгу отсюда.

3 простых способа удалить текст после символа в Excel

Здесь вы узнаете, как удалить текст после определенного символа с помощью команды «Найти и заменить» в Excel, с помощью формул, а также с помощью кода VBA.

1. Используйте команду «Найти и заменить», чтобы удалить текст после символа

Мы узнаем, как удалить текст после определенного символа с помощью функции «Найти и заменить» в Excel. В Excel это самый простой способ удалить любой текст после определенного символа.

Действующие действия приведены ниже,

Шаг 1. Сначала выберите набор данных.

Шаг 2. Затем на вкладке "Главная" выберите "Найти и выбрать" -> "Заменить".

Шаг 3. Во всплывающем окне «Найти и заменить» рядом с меткой Найти что напишите символ, после которого вы хотите удалить текст, и поставьте после него символ звездочки (*). .

Шаг 4. Оставьте поле Заменить на пустым.

Шаг 5. Нажмите «Заменить все».

Чтобы понять больше, посмотрите на следующую картинку, где мы хотим удалить все после запятой (,), поэтому мы пишем символ запятая (,) и ставим Символ звездочки (*) вместе с ним.

Шаг 4. В наборе данных будет удален весь текст после запятой (,).

2. Вставьте формулу Excel для удаления текста после символа

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

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

я. Удалить весь текст после символа

cell = номер ячейки ваших данных

символ = символ, после которого вы хотите удалить текст

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

Шаг 1. В ячейке рядом с набором данных напишите приведенную выше формулу.

Шаг 2. Нажмите Enter.

Он удалит весь текст после определенного символа.

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

Функция ПОИСК определяет позицию символа в ячейке (в нашем случае она определяет позицию запятой (,) в данных ячейки B4) и передает ее функции ВЛЕВО, которая извлекает соответствующий номер символа от начала строки в ячейке.

ii. Удалить текст после N-го появления символа

В ситуации, когда вам нужно удалить текст после определенного символа, но этот символ встречается в строке несколько раз, вам нужно использовать следующую формулу,

cell = номер ячейки ваших данных

символ = символ, после которого вы хотите удалить текст

n – вхождение символа, после которого следует удалить текст.

Этапы удаления текста после N-го вхождения символа приведены ниже,

Шаг 1. В ячейке рядом с набором данных напишите приведенную выше формулу.

Шаг 2. Нажмите Enter.

Он удалит весь текст после определенного символа.

Это даст вам результат усеченного набора данных без всего текста после запятой (,).

iii. Удалить текст после последнего вхождения символа

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

cell = номер ячейки ваших данных

character = символ, после которого вы хотите удалить текст.

Этапы удаления текста после последнего вхождения символа приведены ниже,

Шаг 1. В ячейке рядом с набором данных напишите приведенную выше формулу.

Шаг 2. Нажмите Enter.

Он удалит весь текст после определенного символа.

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

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

ДЛСТР(ПОДСТАВИТЬ(B4, ",""))

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

ДЛСТР(B4) - ДЛСТР(ПОДСТАВИТЬ(B4, ",""))

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

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

Он создаст набор данных имени игрока в крикет без профессий.

3.Внедрение кода VBA для удаления текста после символа в Excel

Если вы опытный пользователь Excel, то этот метод только для вас. Использование VBA для удаления текста после символа — это самый быстрый способ выполнить работу.

Шаг 1. Нажмите Alt + F11 на клавиатуре или перейдите на вкладку "Разработчик" -> Visual Basic, чтобы открыть редактор Visual Basic.

Шаг 2. В строке меню нажмите «Вставить» -> «Модуль».

Шаг 3. Скопируйте следующий код и вставьте его в окно кода.

Теперь ваш код готов к запуску.

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

Шаг 5. Затем перейдите в раздел Разработчик -> Макросы.

Шаг 6. Выберите имя макроса remove_text_after_character -> Выполнить

Он покажет вам результат удаления текста после символа в Excel.

Заключение

В этой статье мы узнали, как удалить текст после определенного символа в Excel. Мы узнаем, как это сделать, не только из 1-го вхождения символа, но также из последнего и N-го вхождения. Я надеюсь, что эта статья была очень полезна для вас. Не стесняйтесь задавать любые вопросы, если у вас есть по теме.

Мы можем захотеть обрезать некоторые символы после определенного символа, чтобы сделать наш набор данных аккуратным и чистым в Excel. Есть много причин для удаления символов, таких как удаление ненужных текстов, сохранение только важных фактов, улучшение организации таблицы данных и т. д. Какова бы ни была причина, вы узнаете 4 потрясающих приема, с помощью которых можно с легкостью удалять символы после определенного символа в Excel.

Загрузить рабочую тетрадь

Рекомендуется загрузить файл Excel и попрактиковаться вместе с ним.

4 приема для удаления символов после определенного символа в Excel

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

Итак, без дальнейших обсуждений, давайте сразу рассмотрим все методы один за другим.

1. Использование поиска и замены для удаления символов в Excel

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

Как мы видим, мы можем легко заменить все символы после запятой в конце их названий, чтобы обрезать названия стран. Для этого

❶ Скопируйте тексты из столбца "Имя и страна" в столбец "Только имя".

❷ Нажмите CTRL + H, чтобы открыть диалоговое окно "Найти и заменить".

❹ Оставьте поле Заменить на пустым.

❺ Нажмите кнопку "Заменить все".

Когда вы выполните все описанные выше шаги, мы увидим, что вы успешно удалили все символы после имен, как показано на изображении ниже:

2.Формула для удаления символов после определенного символа в Excel

Мы можем использовать функции ВЛЕВО и НАЙТИ, чтобы написать формулу, которая может удалять символы после определенного символа в Excel.

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

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

❷ Теперь введите формулу:

❸ Наконец, нажмите кнопку ENTER.

❹ Теперь перетащите значок маркера заполнения в конец столбца "Только имя".

Когда вы выполните все описанные выше шаги, вы увидите, что ваши тексты обрезаны, как показано на рисунке ниже:

␥ Разбивка формулы

НАЙТИ(“,,B5) ▶ находит положение запятой, равное 16.
НАЙТИ(“,,B5)-1 ▶ вычитает возвращаемое значение функции НАЙТИ на 1.
=LEFT(B5,FIND(“,,B5)-1) ▶ сохраняет только первые 15 символов слева.

3. Удалить конечные символы в Excel с помощью Flash Fill

В Excel есть замечательная функция под названием "Мгновенное заполнение". Эта функция может показать некоторый интеллект в выполнении своей задачи. Он может получить намерение пользователя в соответствии с его рабочим процессом.

Например, в нашем наборе данных есть имена со странами, разделенными запятой. И мы хотим удалить символы после запятой.

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

Итак, давайте углубимся в это, чтобы увидеть, как это работает на самом деле:

❶ Выберите ячейку C5 и введите Эндрю Карнеги.

Это текст с символами, за которыми следует запятая.

❷ После этого, глядя на ввод следующего имени, это Стив Джобс.

Как вы можете видеть на рисунке ниже, когда мы набираем S для Стива Джобса, он предлагает все следующие имена без символов после запятых.

Поэтому все, что нам нужно сделать, чтобы принять это предложение, это

❸ Нажмите кнопку ВВОД.

Когда мы выполним все описанные выше шаги, мы увидим желаемый результат, как на картинке ниже:

4. Удалить символы после определенного символа с помощью кода VBA

Мы можем создать определяемую пользователем функцию, используя код VBA для удаления символов после определенного символа в Excel. Теперь выполните следующие действия:

❶ Нажмите ALT + F11, чтобы открыть редактор VBA.

❷ Выберите «Вставить ▶ Модуль».

❸ Скопируйте следующий код VBA:

❹ Вставьте и сохраните код в редакторе VBA.

После этого вернитесь к таблице данных и

❺ Выберите ячейку C5 и введите формулу:

❻ Нажмите кнопку ВВОД.

❼ После этого перетащите значок маркера заполнения в конец столбца "Только имя".

Когда вы выполните все описанные выше шаги, вы удалите нужные символы, как показано на рисунке ниже:

Что нужно помнить

📌 Нажмите CTRL + H, чтобы открыть диалоговое окно «Найти и заменить».

📌 Вы можете нажать ALT + F11, чтобы открыть редактор VBA.

Заключение

Подводя итог, мы обсудили 4 метода удаления символов после определенного символа в Excel. Вам рекомендуется скачать практическую рабочую тетрадь, прилагаемую к этой статье, и практиковать все методы с ней. И не стесняйтесь задавать любые вопросы в разделе комментариев ниже. Мы постараемся ответить на все соответствующие запросы как можно скорее. Посетите наш веб-сайт Exceldemy, чтобы узнать больше.

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