Как оставить определенный текст в Excel

Обновлено: 02.12.2024

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

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

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

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

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

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

Оглавление

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        1. Выберите диапазон ячеек, из которых вы хотите удалить определенный символ.
        2. Нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить.
        3. В поле Найти введите символ.
        4. Оставьте поле Заменитьна пустым.
        5. Нажмите Заменить все.

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

        • Этот метод удаляет символы непосредственно из ваших исходных данных. Если результат отличается от ожидаемого, нажмите Ctrl + Z, чтобы отменить изменение и вернуть исходные данные.
        • Если вы имеете дело с буквенными символами, где регистр букв имеет значение, нажмите Параметры, чтобы развернуть диалоговое окно Найти и заменить, а затем установите флажок Учитывать регистр< /em> для выполнения поиска с учетом регистра.

        Удалить определенный символ из строки с помощью формулы

        Чтобы удалить определенный символ из любой позиции строки, используйте эту общую формулу ПОДСТАВКИ:

        В нашем случае формула принимает следующий вид:

        Введите приведенную выше формулу в ячейку B2, скопируйте ее в ячейку B6, и вы получите следующий результат:

        Обратите внимание, что ПОДСТАВИТЬ всегда возвращает текстовую строку, даже если результат содержит только числа, как в ячейках B2 и B3 (обратите внимание на выравнивание по левому краю, типичное для текстовых значений).

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

        Или вы можете выполнить некоторую математическую операцию, которая не изменит исходное значение, например добавить 0 или умножить на 1:

        Удалить сразу несколько символов

        Чтобы удалить несколько символов с помощью одной формулы, просто вставьте функции ПОДСТАВКИ одну в другую.

        • Функция ЗАМЕНИТЬ чувствительна к регистру, имейте это в виду при работе с буквами.
        • Если вы хотите, чтобы результаты были значениями, независимыми от исходных строк, используйте параметр Специальная вставка — значения, чтобы заменить формулы их значениями.
        • В ситуации, когда нужно удалить много разных символов, намного удобнее использовать пользовательскую функцию RemoveChars, определенную в LAMBDA.

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

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

        Удалить текст из нескольких ячеек

        Чтобы удалить определенный текст из каждой ячейки в выбранном диапазоне, нажмите Ctrl + H, чтобы открыть диалоговое окно Найти и заменить, а затем:

        При нажатии кнопки Заменить все все замены будут выполнены за один раз:

        Удалить определенный текст из ячейки с помощью формулы

        Чтобы удалить часть текстовой строки, вы снова используете функцию ПОДСТАВИТЬ в ее основной форме:

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

        Эта формула переходит в ячейку B2, а затем вы перетаскиваете ее вниз на столько строк, сколько необходимо:

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

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

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

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

        =ПОДСТАВИТЬ(A2, "/", "", 2)

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

        Чтобы удалить первый символ с левой стороны строки, вы можете использовать одну из следующих формул. Оба делают одно и то же, но по-разному.

        В переводе на человеческий язык формула гласит: в указанной ячейке взять 1 символ (num_chars) с 1-й позиции (start_num) и заменить его пустой строкой ("" ).

        Здесь мы вычитаем 1 символ из общей длины строки, которая вычисляется функцией ДЛСТР. Разница передается RIGHT для извлечения этого количества символов с конца.

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

        =ВПРАВО(A2, ДЛСТР(A2) - 1)

        На снимке экрана ниже показана формула REPLACE. Формула RIGHT LEN даст точно такие же результаты.

        Чтобы удалить любые n символов в начале строки, см. статью Удаление символов слева в Excel.

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

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

        Логика аналогична формуле RIGHT LEN из предыдущего примера:

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

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

        =ЛЕВО(A2, ДЛСТР(A2) - 1)

        Чтобы удалить любые n символов с конца строки, см. статью Удаление символов справа в Excel.

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

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

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

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

        =LEFT(A2, ПОИСК(":", A2) -1)

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

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

        Формула удаления лишних пробелов из ячеек проста:

        Где A2 – исходная текстовая строка.

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

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

        Чтобы избавиться от них, преобразуйте неразрывные пробелы в обычные с помощью ЗАМЕНЫ:

        Где 160 — это кодовый номер неразрывного пробела ( ).

        Кроме того, используйте функцию CLEAN для удаления непечатаемых символов:

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

        Дополнительную информацию см. в разделе Как удалить пробелы в Excel.

        Удаление символов в Excel с помощью Flash Fill

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

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

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

        Готово!

        Примечание. Если Excel не может распознать шаблон в ваших данных, заполните еще пару ячеек вручную, чтобы предоставить больше примеров. Кроме того, убедитесь, что Flash Fill включен в вашем Excel. Если это все еще не работает, вам придется прибегнуть к какому-то другому методу.

        Специальные инструменты для удаления символов или текста в Excel

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

        На вкладке Данные Ablebits в группе Текст есть три варианта удаления символов из ячеек Excel:

        • Определенные символы и подстроки
        • Символы в определенной позиции
        • Повторяющиеся символы

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

        1. Нажмите Удалить >Удалить символы.
        2. Выберите наиболее подходящий вариант.
        3. Установите или снимите флажок С учетом регистра.
        4. Нажмите Удалить.

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

        Удалить определенный символ

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

        Например, мы удаляем все вхождения заглавных букв A и B из диапазона A2:A4:

        Удалить предопределенный набор символов

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

        • Непечатаемые символы: удаляется любой из первых 32 символов в 7-битном наборе ASCII (кодовые значения от 0 до 31), включая символ табуляции, разрыв строки и т. д.< /li>
        • Текстовые символы – удаляет текст и сохраняет числа.
        • Цифровые символы – удаляет числа из буквенно-цифровых строк.
        • Символы и знаки препинания – удаляет специальные символы и знаки препинания, такие как точка, вопросительный знак, восклицательный знак, запятая и т. д.

        Удалить часть текста

        Чтобы удалить часть строки, выберите параметр Удалить подстроку.

        Например, чтобы извлечь имена пользователей из адресов Gmail, мы удаляем подстроку "@gmail.com":

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

        Чтобы найти и удалить определенный текст в Excel, мы можем использовать функцию ПОДСТАВИТЬ.

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

        В этом примере мы предоставили ссылку на ячейку (B3), содержимое, которое мы хотим удалить из текста ("-"), а для замещающего текста - пустую строку ("").

        Функция ПОДСТАВИТЬ заменяет все дефисы "-" ничем.

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

        Удалить более одного персонажа

        Мы также можем удалить более одного символа из текста, вложив функции ПОДСТАВКИ.

        Допустим, у нас есть скобки и дефисы в тексте, и мы хотим удалить их оба. Мы вложим функции ПОДСТАВКИ три раза, чтобы получить желаемый результат, например:

        Сначала Excel удалит из текста символ «(», затем перейдет к внешней вложенной функции ПОДСТАВИТЬ и удалит «)», а затем перейдет к следующей внешней функции ПОДСТАВИТЬ и удалит знак дефиса («- ").

        Удалить текст в Google Таблицах

        Формула для удаления текста путем сопоставления содержимого работает точно так же в Google Таблицах, как и в Excel:

        Практический лист Excel

        Попрактикуйтесь в функциях и формулах Excel с помощью наших 100 % бесплатных тренировочных листов!

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

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

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

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

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

        Это руководство охватывает:

        Удалить текст после символа с помощью поиска и замены

        Если вы хотите быстро удалить весь текст после определенной текстовой строки (или перед текстовой строкой), вы можете сделать это с помощью функции "Найти и заменить" и подстановочных знаков.

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

        Ниже приведены шаги для этого:

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

        Поскольку это заменяет текст из выбранных ячеек, рекомендуется скопировать текст в другой столбец, а затем выполнить эту операцию поиска и замены или создать резервную копию ваших данных, чтобы исходные данные остались нетронутыми< /p>

        Как это работает?

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

        Когда я использую его после запятой (в поле «Найти»), а затем нажимаю кнопку «Заменить все», он находит первую запятую в ячейке и считает ее совпадением.

        Это связано с тем, что знак звездочки (*) соответствует всей текстовой строке, следующей за запятой.

        Поэтому, когда вы нажимаете кнопку "Заменить все", запятая и весь последующий текст заменяются.

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

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

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

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

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

        Ниже приведена формула для этого:

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

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

        Это был простой сценарий.

        Возьмем более сложный вариант.

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

        Вот формула, которая это сделает:

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

        Мне нужно как-то узнать положение второй запятой, а затем извлечь все, что находится слева от второй запятой.

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

        Эта позиция восклицательного знака используется в функции LEFT для извлечения всего до второй запятой.

        Но что, если в наборе данных имеется непостоянное количество запятых.

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

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

        Ниже приведена формула, которая это сделает

        Приведенная выше формула использует функцию ДЛСТР для определения общей длины текста в ячейке, а также длины текста без запятой.

        Когда я вычитаю эти два значения, я получаю общее количество запятых в ячейке.

        Итак, это дало бы мне 3 для ячейки A2 и 2 для ячейки A4.

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

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

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

        Удалить текст с помощью быстрой заливки

        Мгновенная заливка – это инструмент, впервые появившийся в Excel 2013 и доступный во всех последующих версиях.

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

        Поэтому, если вы хотите удалить текст до или после определенного символа, вам просто нужно показать flash fairy, как будет выглядеть результат (введя его вручную пару раз), и flash fill автоматически поймет шаблон. и предоставить вам результаты.

        Позвольте мне показать вам это на примере.

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

        Вот как это сделать с помощью Flash Fill:

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

        Вы также можете использовать сочетание клавиш "Быстрая заливка" Control + E после выбора ячеек в столбце результатов (столбец B в нашем примере)

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

        Поэтому убедитесь, что вы перепроверили результаты Flash Fill

        И точно так же, как мы удалили весь текст после определенного символа с помощью мгновенной заливки, вы можете использовать те же шаги для удаления текста перед определенным символом. просто покажите flash fill, как в результате должен выглядеть мой интернет вручную в соседней колонке, и он сделает все остальное.

        Удалить текст с помощью VBA (пользовательская функция)

        Вся концепция удаления текста до или после определенного символа зависит от нахождения положения этого символа.

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

        Если вам приходится делать это довольно часто, вы можете упростить этот процесс, создав пользовательскую функцию с помощью VBA (называемую функциями, определяемыми пользователем).

        После создания вы можете использовать эту функцию снова и снова. Он также намного проще и удобнее в использовании (поскольку большая часть тяжелой работы выполняется кодом VBA в серверной части).

        Ниже приведен код VBA, который можно использовать для создания пользовательской функции в Excel:

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

        Эта функция принимает 2 аргумента:

        1. Ссылка на ячейку, для которой вы хотите найти последнее вхождение определенного символа или текстовой строки.
        2. Символ или текстовая строка, положение которой необходимо найти

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

        Ниже приведена формула, которая это сделает:

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

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

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

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

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

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