Как удалить часть текста из ячейки в Excel

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

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

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

Введите приведенную выше формулу в ячейку 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.

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

  
• Как прошить alcatel one touch pop c5 с флешки
  
• Программа для создания загрузочной флешки uefi
  
• Как сделать видео черно-белым в After Effects
  
• Как активировать AutoCAD 2013
  
• Shazam, что это за приложение для Android