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

Обновлено: 03.07.2024

Бывший писатель Lifewire Тед Френч является сертифицированным специалистом Microsoft, который преподает и пишет о электронных таблицах и программах для работы с электронными таблицами.

Что нужно знать

Чтобы удалить символы ASCII, введите =CLEAN(Text).
Используйте функцию ЗАМЕНА, чтобы преобразовать символы Юникода в символы ASCII, которые можно удалить с помощью функции ОЧИСТКА.

Вот как использовать функцию очистки в Microsoft Excel 2019, 2016, 2013, 2010 и Excel для Microsoft 365, чтобы удалить многие непечатаемые компьютерные символы, скопированные или импортированные на лист. Такие символы могут мешать печати, сортировке и фильтрации данных.

Синтаксис и аргументы функции CLEAN

Синтаксис функции – это ее структура, включающая имя, квадратные скобки и аргументы. Синтаксис функции CLEAN:

Текст

в расположение этих данных на листе, который вы хотите очистить.

Например, ячейка A2 содержит следующую формулу:

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

В результате останется только слово

Помимо удаления непечатаемых символов, функция ОЧИСТКА также преобразует числа в текст, что может привести к ошибкам, если вы позже будете использовать эти данные в вычислениях.

Удаление непечатаемых символов, не входящих в ASCII

Хотя функция CLEAN отлично подходит для удаления непечатаемых символов ASCII, есть несколько непечатаемых символов, выходящих за пределы диапазона ASCII, которые вы, возможно, захотите удалить.

К непечатаемым символам Unicode относятся числа 129, 141, 143, 144 и 157. Кроме того, вы можете удалить 127, который является удаляемым символом и также является непечатаемым.

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

Кроме того, можно просто заменить оскорбительный непечатаемый символ ничем ("").

Что такое непечатаемые символы?

Каждый символ на компьютере — печатаемый и непечатаемый — имеет число, известное как код или значение символа Unicode. Другой, более старый и более известный набор символов — ASCII, обозначающий Американский стандартный код для обмена информацией, — был включен в набор Unicode.

В результате первые 32 символа (от 0 до 31) наборов Unicode и ASCII идентичны. Они используются программами для управления периферийными устройствами, такими как принтеры, на разных платформах. Таким образом, они не предназначены для использования на листе и могут вызвать ошибки, если они присутствуют.

Функция CLEAN, предшествующая набору символов Unicode, удаляет первые 32 непечатаемых символа ASCII и те же символы из набора Unicode.

Зарегистрированный пользователь Дата присоединения 27 марта 2013 г. Местоположение Йоркшир, Англия MS-Off Ver Excel 2007 Сообщения 8

Удалить скрытые символы в Excel

Я импортировал электронную таблицу Excel из Инструментов Google для веб-мастеров, и вся информация заканчивается разрывом строки.

Кто-нибудь знает, как удалить скрытые символы, такие как разрыв строки, из Excel 2010, пожалуйста?

Это необходимо для моей работы.

Мы очень признательны за любую помощь.

Уважаемый участник форума Регистрация 26.10.2008 Местоположение Бирмингем, Великобритания MS-Off Ver Все версии до 2010 Сообщений 1025

Привет, Джеймс
1. Нажмите Ctrl+F, чтобы отобразить вкладку «Поиск» диалогового окна «Найти и заменить».
2. В поле «Найти что» удерживайте нажатой клавишу «Alt» и введите 0010 на цифровой клавиатуре. Может показаться, что в поле «Найти что» ничего нет, но персонаж там есть.
3. Щелкните Заменить все.
Тони

Гуру форума Регистрация 23.06.2007 Местоположение Лондон, Англия MS-Off Ver office 97 ,2007 Сообщений 19,320

какой код символа? используйте =code(right(a1)) чтобы получить его
вы можете найти массовую замену, используя этот код, используя найти/заменить найти, удерживая нажатой клавишу alt, и введите его как 3-значный код, добавив начальный 0, если необходимо, но вы должны использовать цифровую клавиатуру (это немного ударило и промахнулось, так как иногда перестает работать)
заменить оставить это пустое нажатие вернуть
в качестве альтернативы вы можете попробовать = очистить (a1) заполнить затем скопируйте вставку как вставку/специальные/ значения
или, если это не сработает, вы можете попробовать = ПОДСТАВИТЬ (A1, CHAR (10), ""), где 10 соответствует любому коду, который вы обнаружили.
или вы можете попробовать ввести текст в столбцы и посмотреть, сможете ли вы удалить его там (просто выберите данные столбца/текст в столбцы, фиксированная ширина, дважды щелкните любые разрывы, чтобы удалить их, нажмите «Готово»), но чтобы быть уверенным, что нам нужно посмотреть образец

"Если не указано иное, все мои комментарии адресованы OP"

Извините, я пробовал это, и это не сработало.

Гуру форума Регистрация 23.06.2007 Местоположение Лондон, Англия MS-Off Ver office 97 ,2007 Сообщений 19,320

Зарегистрированный пользователь Дата присоединения 27-12-2012 Местоположение US MS-Off Ver Excel 2010 Сообщения 41

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

Гуру форума Регистрация 23.06.2007 Местоположение Лондон, Англия MS-Off Ver office 97 ,2007 Сообщений 19,320

Здравствуйте, прикреплена электронная таблица с URL-адресами из инструментов Google для веб-мастеров.

Когда я копирую и вставляю URL-адреса в Word, появляются скрытые абзацы.

Я попробовал рекомендации, рекомендованные выше.

Спасибо за всю вашу помощь в этой отправке.

Гуру форума Регистрация 23.06.2007 Местоположение Лондон, Англия MS-Off Ver office 97 ,2007 Сообщений 19,320

ничего не видно, когда я открываю, все выглядит нормально и выглядит нормально при вставке в слово
� отображается как char 182
вы пытались скопировать его, а затем вставить в поле поиска find/ заменить

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

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

В ячейке, содержащей одно из маленьких полей, выделите поле и нажмите Ctrl+C. Это скопирует символ в буфер обмена.
Выберите «Заменить» в меню «Правка» или нажмите Ctrl+H. Excel отображает вкладку «Заменить» диалогового окна «Найти и заменить». (См. рис. 1.)

Рис. 1. Вкладка «Заменить» диалогового окна «Найти и заменить».

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

Одна вещь, которую стоит попробовать, – это использовать Word в операциях по "очистке". Скопируйте данные из Excel в документ Word (вставьте как обычный текст), а затем замените неверные символы. Затем вы можете вставить данные обратно в Excel. Некоторые люди сообщают, что получают именно те результаты, которые им нужны, используя такой двусторонний подход к работе с данными.

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

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

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

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

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

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

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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

Скачать рабочую тетрадь

Для практики вы можете скачать учебник по ссылке ниже.

4 метода удаления специальных символов в Excel

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

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

1. Удаление специальных символов в Excel с помощью формул Excel

В Excel есть полезные формулы, которые можно использовать для удаления специальных символов в Excel. Они формируются с использованием таких функций, как ПОДСТАВИТЬ, ВПРАВО, ВЛЕВО, ОЧИСТИТЬ, ОБРЕЗАТЬ и ЗАМЕНИТЬ. Мы рассмотрим каждый из них один за другим.

а. Использование функции ПОДСТАВИТЬ

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

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

Формула для удаления определенных символов с помощью ЗАМЕНЫ будет выглядеть так:

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

Формула будет такой:

Однако вы можете удалить все символы, сохранив только имя.

На этот раз формула будет вложена SUBSTITUTE внутри себя. Формула будет выглядеть так:

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

Описание формулы:

Синтаксис формулы:

text=текст, с которым вы хотите работать.

old_text= текст, который вы хотите удалить.

new_text= замененный текст. (В нашем случае мы заменяем его пробелом « «).

instance_name= номер специального символа в случае присутствия в тексте рекурсивных символов.

Особый случай:

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

Функция ВПРАВО или ВЛЕВО используется для получения местоположения символа, код которого вы хотите получить.

Поэтому этот процесс включает два этапа:

Получение кода с использованием формулы CODE, вложенной с RIGHT или LEFT.
Используя формулу SUBSTITUTE и вместо old_text напишите CHAR(число).

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

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

б. Использование функций ВПРАВО или ВЛЕВО

Учитывая, что выше вы уже видели использование функций ВПРАВО и ВЛЕВО. Их можно использовать с функцией ДЛСТР для удаления определенных символов в Excel.

Формула будет такой:

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

Для этого используется следующая формула:

Аналогично для ЛЕВОЙ формулы

А для увеличения номера экземпляра изменена формула:

Описание формулы:

Синтаксис формулы:

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

num_chars= количество удаляемых символов.

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

-1 или -(любое число) — это количество символов, которое нужно вычесть из общего количества символов в тексте.

в. Использование функций CLEAN и TRIM

Набор данных может содержать непечатаемые символы и лишние пробелы. Для их удаления можно использовать функции CLEAN и TRIM.

Формула для удаления непечатаемых символов:

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

Тем не менее, вы можете сделать и то, и другое, вложив TRIM и CLEAN с SUBSTITUTE. Формула будет выглядеть так:

Следуйте картинке ниже.

Описание формулы:

Синтаксис отдельной формулы:

Здесь text= текст, из которого вы хотите удалить непечатаемый символ.

text= текст, из которого необходимо удалить лишний пробел.

text=текст, с которым вы хотите работать.

old_text= текст, который вы хотите удалить.

new_text= замененный текст. (В нашем случае мы заменяем его пробелом « «).

instance_name= номер специального символа в случае присутствия в тексте рекурсивных символов.

д. Использование функции ЗАМЕНА

Кроме того, существует еще одна формула, использующая функцию ЗАМЕНИТЬ для удаления определенного количества символов после определенного количества символов.

Здесь формула очень похожа на ПОДСТАВИТЬ. Он принимает еще 2 аргумента с именем start_num (число, из которого нужно удалить символы).

num_chars (количество удаляемых символов).

И он не принимает текст в качестве аргумента, необходимого для ЗАМЕНЫ.

2. Использование быстрой заливки для удаления специальных символов в Excel

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

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

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

Нажмите клавишу ВВОД на клавиатуре. Это покажет результат, как показано ниже.

3. Использование команды «Найти и заменить» для удаления специальных символов

Еще один полезный инструмент Excel — «Найти и заменить».

Предположим, мы хотим удалить «Mailto:» перед адресом в столбце с именем «Почтовый адрес» набора данных.

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

Выберите «Заменить» в меню «Найти и заменить». Следуйте рисунку ниже, чтобы найти и заменить из параметров редактирования на вкладке "Главная".

Откроется диалоговое окно. Напишите символ, который вы хотите удалить, в поле «Найти:» и оставьте поле «Заменить на:» пустым. Смотрите рисунок ниже.

Нажмите «Заменить все», и откроется новое окно. Он покажет количество выполненных замен.
Нажмите "ОК".

Вы увидите результат следующим образом.

4. Удаление специальных символов с помощью инструмента Power Query

Конечно, если вы используете Microsoft Excel 2016 или Excel 365, вы можете использовать Power Query для удаления специальных символов в Excel.

Если вы используете Microsoft Excel 2010 или 2013, вы можете установить его с веб-сайта Microsoft.

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

Выберите диапазон данных вместе с заголовком.
Затем выберите «Из таблицы/диапазона» на вкладке «Данные».

Вы найдете небольшую коробку. Проверьте диапазон выбранных данных и установите флажок «Моя таблица имеет заголовки».
После этого нажмите "ОК".

Откроется новое окно под названием Power Query Window.

Выберите «Пользовательский столбец» на вкладке «Добавить столбец» в окне Power Query.

Откроется окно "Пользовательский столбец".
Напишите «Без специальных символов» в параметре «Новое имя столбца». Вы можете написать любое имя.
Затем напишите приведенную ниже формулу в параметре формулы "Пользовательский столбец".

Формула:

Будет создан новый столбец, и ваша новая формула отобразится в строке формул окна.

Выберите «Закрыть и загрузить» на вкладке «Файл» окна.

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

Вы можете заметить, что этот процесс не удалил символы «^^» из ячейки D7. Это потому, что Excel рассматривает символ в категории «. ». персонаж.

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

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

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

Заключение

В статье описаны 4 разных способа удаления специальных символов в Excel. Мы использовали формулы и инструменты Excel, чтобы объяснить 4 различных способа удаления специальных символов в Excel. сделать это. Короче говоря, формулы включают такие функции, как ПОДСТАВИТЬ, ОЧИСТИТЬ, ПРАВИЛЬНО, КОД и так далее. С другой стороны, используются такие инструменты, как Flash Fill, Find & Replace и Power Query. Я надеюсь, что эта статья была вам полезна. По любым дополнительным вопросам пишите в разделе комментариев.

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