Найти и удалить символ в Excel

Обновлено: 04.07.2024

Большая часть обработки данных связана с очисткой данных.

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

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

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

Оглавление

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

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

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

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

Ниже приведены шаги по удалению определенного символа с помощью функции «Найти и заменить»:

Это удалит все экземпляры символа @ из всех ячеек.

Удаление определенного символа с помощью функции ПОДСТАВИТЬ

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

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

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

Эта формула заменит old_character пробелом (""), что означает, что символ будет удален.

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

Для этого вы можете использовать функцию ПОДСТАВИТЬ со следующими шагами:

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

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

Что, если вы хотите удалить только первый символ «@» из каждой ячейки, а не все их экземпляры?

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

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

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

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

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

Удаление любого специального символа с помощью функции CLEAN

Функция Excel CLEAN удаляет разрывы строк и непечатаемые символы из строки. Общий синтаксис этой функции:

Здесь original_string — это текст или ссылка на текстовую ячейку, которую вы хотите очистить.

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

Давайте посмотрим на следующий набор строк.

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

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

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

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

Функция CLEAN удаляет только первые 32 (непечатаемых) символа в 7-битном коде ASCII (т. е. значения от 0 до 31). Однако в Unicode есть и другие непечатаемые символы, которые CLEAN не может удалить.

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

Удаление начальных и конечных пробелов с помощью функции TRIM

Большая часть очистки данных состоит просто в удалении начальных или конечных пробелов из строк. Функция TRIM в Excel позволяет легко сделать это за один раз.

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

Общий синтаксис этой функции:

Здесь original_string — это текст или ссылка на текстовую ячейку, которую вы хотите обработать.

Давайте воспользуемся функцией TRIM, чтобы удалить символы пробела, оставшиеся после применения функции CLEAN:

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

Удаление определенного невидимого символа из строки с помощью функций SUBSTITUTE, CHAR и CODE

В некоторых случаях обе функции CLEAN и TRIM не могут удалить из строки особенно раздражающие символы.

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

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

Поскольку его нельзя было удалить с помощью TRIM или CLEAN, совершенно очевидно, что это не обычный пробел.

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

Чтобы узнать, что это за символ, мы можем использовать функцию КОД. Введите эту функцию в ячейку B2: =CODE(LEFT(A2)). Так как символ находится на первой позиции в тексте, мы можем легко узнать его код с помощью функции ВЛЕВО. В этом случае мы получаем результат как «160». Это означает, что код невидимого символа — 160.
Используем это значение в функции ПОДСТАВИТЬ. Введите эту функцию в ячейку C2: = ПОДСТАВИТЬ (A2, СИМВОЛ (B2)», «»). Здесь мы использовали функцию CHAR, чтобы преобразовать код символа обратно в его символьный эквивалент.
Когда вы сейчас нажмете клавишу возврата, вы обнаружите, что все экземпляры этого невидимого символа удалены из строки.

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

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

Чтобы удалить пробелы и специальные символы (которые часто сопровождают данные, загруженные из других приложений), вы можете использовать функции CLEAN и TRIM соответственно.

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

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

< /p>

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

Предположим, у вас есть диапазон данных, и вы хотите удалить определенный символ из каждой строки, например символ «-», как показано на снимке экрана ниже, в Excel. Есть ли у вас хорошая идея для решения этой задачи? Теперь я расскажу вам о некоторых хитростях, позволяющих удалить определенный символ из строк ячеек в Excel.

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

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

В Excel вы можете сначала узнать все символы «-», а затем заменить символ ничем.

Совет. Если вы хотите удалить символ алфавита, и вы хотите найти его с учетом регистра, вы можете нажать "Параметры", чтобы развернуть диалоговое окно, и установить флажок "Учитывать регистр".

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

Если у вас есть Kutools for Excel, вы можете использовать утилиту «Удалить символы», чтобы быстро удалить определенные символы из строки.

После установки Kutools for Excel сделайте следующее: (Бесплатно загрузите Kutools for Excel сейчас!)

Совет. Если вы хотите удалить несколько символов, таких как "-" и "K", из строк, просто введите символы, которые вы хотите удалить, "-K" в пользовательское текстовое поле. Смотрите скриншот:

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

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

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

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

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

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

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

Этот метод удаляет символы непосредственно из ваших исходных данных. Если результат отличается от ожидаемого, нажмите 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 может оказать вам услугу и автоматически удалить символы или часть текста на основе предоставленного вами шаблона.

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

Вставьте пустой столбец справа от исходных данных.

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

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

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

Готово!

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

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

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

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

Определенные символы и подстроки

Символы в определенной позиции

Повторяющиеся символы

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

Нажмите Удалить >Удалить символы.

Выберите наиболее подходящий вариант.

Установите или снимите флажок С учетом регистра.

Нажмите Удалить.

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

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

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

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

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

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

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

Цифровые символы – удаляет числа из буквенно-цифровых строк.

Символы и знаки препинания – удаляет специальные символы и знаки препинания, такие как точка, вопросительный знак, восклицательный знак, запятая и т. д.

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

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

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

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

В этом посте рассказывается, как удалить определенный символ из текстовой ячейки в Excel. Как удалить определенный символ из ячейки с формулой в Excel 2013/2016.

Метод 1: удалить определенный символ с помощью функции «Найти и заменить»

Шаг 1: выберите один диапазон, из которого вы хотите удалить определенный символ. Например: B1:B5.

Шаг 2. Перейдите на вкладку «Главная» и нажмите команду «Найти и выбрать» в группе «Редактирование». И выберите «Заменить» в контекстном меню. Откроется диалоговое окно «Найти и заменить».

Шаг 4: нажмите кнопку «Заменить все». И диалоговое окно подскажет вам, сколько символов заменено в выбранном вами диапазоне ячеек. Нажмите кнопку «Закрыть», чтобы закрыть диалоговое окно «Найти и заменить».

Метод 2: удалить определенный символ с помощью формулы

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

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

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

Связанные функции

Функция Excel ПОДСТАВИТЬ заменяет новую текстовую строку на старую текстовую строку в текстовой строке. Синтаксис функции ПОДСТАВИТЬ следующий: = ПОДСТАВИТЬ (текст, старый_текст, новый_текст, [номер_экземпляра])….
Функция Excel CHAR возвращает символ, заданный числом (значение ASCII). Синтаксис функции CHAR следующий: =CHAR(число)….

В этом посте рассказывается, как найти и заменить сразу несколько значений с помощью макроса VBA или формулы в Excel. Как сделать множественный поиск и замену в Excel. Предположим, что у вас есть несколько ячеек, содержащих .

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

Предположим, что вы перечислили некоторый текст в одной ячейке, разделенной разрывом строки (вы можете сделать это, нажав ALT + ENTER после ввода текста), и теперь вы хотите извлечь несколько строк текста.

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

Мы часто вводим даты в таблицы Excel, и мы можем ввести текущую дату, быстро нажав «Ctrl +;» одновременно. Если в некоторых случаях мы вводим формат даты, который нам не нужен, мы должны переключить формат даты.

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

В этом посте рассказывается, как подсчитывать значения в одной ячейке, разделенные запятыми, с помощью формулы в Excel. Как подсчитать значения, разделенные запятыми, в одной ячейке в Excel 2013/2016. Можно ли иметь .

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

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

В этом посте объясняется, как удалить разрывы строк в наборе данных в Excel. Как удалить возврат каретки или разрывы строк с помощью формулы в Excel. Как удалить разрывы строк с помощью макроса VBA в Excel. .

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


Программное обеспечение для поворота видео для Android

Что такое Adobe Self Extractor

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

Графический драйвер Intel что это за программа и нужна ли она

Autocad, как удалить точку на полилинии