Непечатаемые символы в Excel

Обновлено: 06.07.2024

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

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

Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

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

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

Функция CLEAN может удалить все непечатаемые символы из ячеек. Например, в столбце C (C2:C11) нужно удалить непечатаемые символы. Вы можете сделать следующее руководство:

<р>1. Выберите пустую ячейку D2 рядом с C2 и введите: «=CLEAN(C2)».

<р>2. Нажмите клавишу ввода.

<р>3. Затем перетащите дескриптор заполнения на диапазон ячеек, из которых вы хотите удалить непечатаемые символы. И все непечатаемые символы были удалены. Смотрите скриншот:

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

С помощью Kutools for Excel's Удалить символы вы можете быстро и легко удалить непечатаемые символы из ячеек.

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Получить сейчас

Шаг 1. Выберите диапазон, с которым вы хотите работать.

Шаг 2. Нажмите Kutools > Текстовые инструменты в группе редактирования > Удалить символы. Смотрите скриншот:

Шаг 3. Установите флажок Непечатаемый во всплывающем диалоговом окне, см. снимок экрана:

Шаг 4. Нажмите "ОК" или "Применить", чтобы удалить непечатаемые символы в диапазоне.

Каждый печатный или непечатаемый символ имеет код или значение Unicode. Первые 32 символа (0-31) Unicode или ASCII в программах для управления различными периферийными устройствами. Использование их на листах Excel может привести к ошибкам. В этой статье мы используем такие функции, как CLEAN, SUBSTITUTE, такие функции, как Find & Replace, и код макроса VBA для удаления непечатаемых символов.

Допустим, мы копируем данные из внешних источников, содержащие непечатаемые символы.

Набор данных для загрузки

4 простых способа удаления непечатаемых символов в Excel

Способ 1. Использование функции CLEAN

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

Шаг 1. Щелкните любую ячейку (D3). Введите формулу =ЧИСТЫЙ(C3).

Шаг 2. Нажмите ENTER. Он удаляет непечатаемые символы.

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

CLEAN Функция не может удалять непечатаемые пробелы.

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

Функция ЗАМЕНА может удалить широкий спектр непечатаемых символов и пробелов.

Способ 2.1. Удаление символов

Шаг 1. Вставьте =SUBSTITUTE(C3,CHAR(11)","") в любую из нужных ячеек (D3).

Шаг 2. Нажмите клавишу ВВОД. Выполнение шагов удаляет непечатаемые символы.

Шаг 3: Введите конкретные номера символов внутри формулы (CHAR(7), CHAR(12) и т. д.) по отдельности после нажатия клавиши ENTER. Окончательный результат аналогичен изображению ниже.

Метод 2.2: удаление пробелов

Шаг 1. Вставьте =ПОДСТАВИТЬ(C7, ",") в любую ячейку (D7).

Шаг 2. Нажмите клавишу ВВОД. Символы в эталонной ячейке удаляются и отображаются в выбранной ячейке.

Способ 3. Использование функции «Найти и заменить»

Функция «Найти и заменить» может найти любой печатный или непечатаемый символ и заменить его любым выбранным вами символом.

Шаг 1. Нажмите одновременно клавиши CTRL+H. Появится окно «Найти и заменить».

Шаг 2. В поле «Найти команду» введите символы (Char(11), Char(7) и т. д.), присутствующие в наборе данных. Нажмите «Найти все»; Найдите, какие символы, существующие в наборе данных, появятся.

Шаг 3. В поле «Заменить командой» введите Char(32), представляющий символ ПРОБЕЛ. Нажмите «Заменить все»; Непечатаемые символы заменяются пробелами.

Шаг 4. Нажмите "ОК".

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

Способ 4. Использование кода макроса VBA

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

Шаг 1: одновременно нажмите клавиши ALT+F11, чтобы открыть окно Microsoft Visual Basic.

Шаг 2. На панели инструментов нажмите "Вставить и выбрать модуль".

Шаг 3. Вставьте приведенный ниже код в модуль

В коде мы объявили функцию RemoveNP с двумя возможными параметрами. Здесь мы объявили символы, которые могут существовать в нашем наборе данных, или точные символы (Char(7), Char(11) и т. д.) существуют в нашем наборе данных.

Затем с помощью функции RemoveNP символы заменяются строкой (’’ ’’).

Шаг 4. Перейдите на рабочий лист и введите =RemoveNP(C3) в любую ячейку (D3).

Шаг 5. Нажмите ENTER. Символы в ячейке C3 удаляются.

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

Заключение

Функции CLEAN, SUBSTITUTE с легкостью удаляют непечатаемые символы. Хотя функция CLEAN имеет свои ограничения, функция SUBSTITUTE или Find & Replace работает практически для всех символов. Объявление символов в макрокоде VBA Еще никогда не было так удобно удалять все виды непечатаемых символов. Надеюсь, вы найдете описанные выше методы очень простыми в использовании. Комментируйте, если вам нужны дополнительные разъяснения или вам есть что добавить.

Есть ли в MS Excel 2010 параметр, который будет отображать непечатаемые символы в ячейке (например, пробелы или символ разрыва строки, вводимый нажатием клавиш Alt-Enter)?

вы можете поместить =IF(CLEAN(A1)=A1,"NA","Needs Cleaning") в ячейку рядом с ячейкой рядом с ней, или вы можете использовать условное форматирование, используя char() (или chr в VBA) для поиска возврата каретки (char(13)) или любого другого символа, вот ссылка на номера символов

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

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

@scott Большое спасибо за примечание об использовании CLEAN(). Эта функция будет очень полезна, хотя, к сожалению, она не поможет в этой конкретной ситуации.

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

7 ответов 7

Хотя вы не можете отображать специальные символы непосредственно в ячейке, вы можете использовать формулу в соседнем (вставленном) столбце, чтобы заменить символы ввода и пробелы на символы по вашему выбору!

Например. заменит любой разрыв строки символом слова для разрыва строки. А вложенная формула заменит и пробел, и ввод. (Примечание: чтобы ввести «Ввод» в формулу, вам нужно нажать Alt+Enter при редактировании формулы.

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

К сожалению, у меня нет хорошего примера такого шрифта, чтобы предоставить вам его, но очень легко добавить маленькую точку к существующему шрифту, используя любой редактор шрифтов. Только не забудьте переименовать шрифт (не файл шрифта, а ИМЯ шрифта внутри файла шрифта), чтобы было легко отличить этот пользовательский шрифт от исходного, если у вас установлены оба.

EDIT Наконец-то я нашел время сделать такой шрифт! А вот и DottedSpace Mono, основанный на Bitstream Vera Sans Mono, но со встроенными точечными пробелами:

CTRL+H заменяет все пробелы на ~ Это быстро поможет для пробелов без программирования , а для обратного просто замените ~ на " ".

Лучшая программа, которую я нашел для сравнения этих типов файлов, в которых не отображается текст, — это Ultra Edit. Пришлось использовать его для сравнения файлов EDI, интерфейсных файлов, технических загрузок и т. д. MS Office просто не подходит для этой задачи.

Этот метод заменит пробелы, но не все непечатаемые символы, например. CHAR(10) (возврат каретки внутри ячейки, также достигается с помощью Alt-Enter )

Изменение шрифта на тип "Терминал" поможет вам увидеть и изменить их.

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

для одинарных кавычек или это

для двойных кавычек. Это заключает в кавычки любой введенный текст. Я также установил шрифт Courier New (или любой другой шрифт фиксированной ширины).

1 Используйте найти и введите пробел

2 Выполните Заменить все и введите "[s-p-a-c-e]"

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

Результат: эти надоедливые пробелы будут видны очень четко

Зачем мне это нужно: я использовал функцию COUNTA, чтобы найти непустые ячейки в столбце. Однако он возвращал число больше, чем я ожидал. Я отлаживал каждую ячейку одну за другой, и, к моему изумлению, некоторые явно пустые ячейки показывали COUNTA=0, а другие показывали COUNTA=1, что не имеет смысла. Я НЕ мог увидеть разницу между ними. Оказывается, в этой функции учитывается единственный оставшийся пробел, но он не виден НИГДЕ ни в ячейке, ни в поле ввода вверху.

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

Если вы работаете с файлами, полученными из источника, отличного от 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, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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