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

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

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

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

Вместо того чтобы удалять их вручную можно воспользоваться специальной функцией Excel ПЕЧСИМВ для удаления всех непечатаемых символов из текста:

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

Функция ПЕЧСИМВ удаляет из указанного текста неправильные символы, которые нельзя напечатать.

Данную функцию лучше поместить как аргумент для функции СЖПРОБЕЛЫ. Благодаря такой формуле мы одновременно очищаем текст от непечатных символов и лишних пробелов или отступов.

Зачистка текста

Зачастую текст, который достается нам для работы в ячейках листа Microsoft Excel далек от совершенства. Если он был введен другими пользователями (или выгружен из какой-нибудь корпоративной БД или ERP-системы) не совсем корректно, то он легко может содержать:

• лишние пробелы перед, после или между словами (для красоты!)
• ненужные символы («г.» перед названием города)
• невидимые непечатаемые символы (неразрывный пробел, оставшийся после копирования из Word или «кривой» выгрузки из 1С, переносы строк, табуляция)
• апострофы (текстовый префикс – спецсимвол, задающий текстовый формат у ячейки)

Давайте рассмотрим способы избавления от такого «мусора».

Замена

«Старый, но не устаревший» трюк. Выделяем зачищаемый диапазон ячеек и используем инструмент Заменить с вкладки Главная – Найти и выделить (Home – Find & Select – Replace) или жмем сочетание клавиш Ctrl+H.

Изначально это окно было задумано для оптовой замены одного текста на другой по принципу «найди Маша – замени на Петя», но мы его, в данном случае, можем использовать его и для удаления лишнего текста. Например, в первую строку вводим «г.» (без кавычек!), а во вторую не вводим ничего и жмем кнопку Заменить все (Replace All). Excel удалит все символы «г.» перед названиями городов:

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

Удаление пробелов

Если из текста нужно удалить вообще все пробелы (например они стоят как тысячные разделители внутри больших чисел), то можно использовать ту же замену: нажать Ctrl+H, в первую строку ввести пробел, во вторую ничего не вводить и нажать кнопку Заменить все (Replace All).

Однако, часто возникает ситуация, когда удалить надо не все подряд пробелы, а только лишние – иначе все слова слипнутся друг с другом. В арсенале Excel есть специальная функция для этого – СЖПРОБЕЛЫ (TRIM) из категории Текстовые. Она удаляет из текста все пробелы, кроме одиночных пробелов между словами, т.е. мы получим на выходе как раз то, что нужно:

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

В некоторых случаях, однако, функция СЖПРОБЕЛЫ (TRIM) может не помочь. Иногда то, что выглядит как пробел – на самом деле пробелом не является, а представляет собой невидимый спецсимвол (неразрывный пробел, перенос строки, табуляцию и т.д.). У таких символов внутренний символьный код отличается от кода пробела (32), поэтому функция СЖПРОБЕЛЫ не может их «зачистить».

Вариантов решения два:

• Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их (Ctrl+C) и вставить (Ctrl+V) в первую строку в окне замены (Ctrl+H). Затем нажать кнопку Заменить все (Replace All) для удаления.
• Использовать функцию ПЕЧСИМВ (CLEAN) . Эта функция работает аналогично функции СЖПРОБЕЛЫ, но удаляет из текста не пробелы, а непечатаемые знаки. К сожалению, она тоже способна справится не со всеми спецсимволами, но большинство из них с ее помощью можно убрать.

Функция ПОДСТАВИТЬ

Замену одних символов на другие можно реализовать и с помощью формул. Для этого в категории Текстовые в Excel есть функция ПОДСТАВИТЬ (SUBSTITUTE) . У нее три обязательных аргумента:

• Текст в котором производим замену
• Старый текст – тот, который заменяем
• Новый текст – тот, на который заменяем

С ее помощью можно легко избавиться от ошибок (замена «а» на «о»), лишних пробелов (замена их на пустую строку «»), убрать из чисел лишние разделители (не забудьте умножить потом результат на 1, чтобы текст стал числом):

Удаление апострофов в начале ячеек

Апостроф (‘) в начале ячейки на листе Microsoft Excel – это специальный символ, официально называемый текстовым префиксом. Он нужен для того, чтобы дать понять Excel, что все последующее содержимое ячейки нужно воспринимать как текст, а не как число. По сути, он служит удобной альтернативой предварительной установке текстового формата для ячейки (Главная – Число – Текстовый) и для ввода длинных последовательностей цифр (номеров банковских счетов, кредитных карт, инвентарных номеров и т.д.) он просто незаменим. Но иногда он оказывается в ячейках против нашей воли (после выгрузок из корпоративных баз данных, например) и начинает мешать расчетам. Чтобы его удалить, придется использовать небольшой макрос. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то апострофы перед содержимым выделенных ячеек исчезнут.

Английские буквы вместо русских

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

Можно, конечно, вручную заменять символы латинцы на соответствующую им кириллицу, но гораздо быстрее будет сделать это с помощью макроса. Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте новый модуль (меню Insert — Module) и введите туда его текст:

Теперь, если выделить на листе диапазон и запустить наш макрос (Alt+F8 или вкладка Разработчик – кнопка Макросы), то все английские буквы, найденные в выделенных ячейках, будут заменены на равноценные им русские. Только будьте осторожны, чтобы не заменить случайно нужную вам латиницу 🙂

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

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

Как деинсталлировать лишние символы

Такая процедура в MS Excel обычно выполняется с помощью специальной функции, встроенной в программу. Однако есть и другие способы осуществления поставленной задачи, которые будут подробно описаны далее.

Способ 1. Использование формулы

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

1. Проанализировать исходный табличный массив, в котором присутствуют «непонятные» символы.
2. Создать дополнительный столбец, в котором будет прописываться очищенный от непечатаемых символов текст.
3. Поставить курсор мышки в строчку второго столбца напротив соответствующей строки из первого столбика.
4. В выделенном элементе прописать вручную с клавиатуры формулу «ПЕЧСИМВ()». Это стандартная функция программы MS Excel, предназначенная для деинсталляции лишних знаков из таблиц, которые нельзя напечатать. В данном примере формула будет записываться следующим образом: «=СЖПРОБЕЛЫ(ПЕЧСИМВ(А2))».

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

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

Способ 2. Удаление непечатаемых символов вручную

Такой метод может сработать лишь в редких ситуациях. Однако в рамках данной статьи его также нужно рассмотреть для полного понимания темы. Ручная деинсталляция лишних знаках в ячейках Excel предполагает соблюдения следующей инструкции:

1. Левой кнопкой компьютерного манипулятора выделить ячейку, в которой присутствуют непечатаемые символы.
2. Переместить курсор мышки в строку для ввода формул, находящуюся под блоком инструментов Microsoft Office Excel.
3. Поставить курсор после «проблемного» знака и попытаться его удалить нажатием на клавишу «Backspace» c клавиатуры ПК.
4. Проверить результат. Возможно, значок удастся удалить, если нет, то стоит воспользоваться первым способом решения проблемы.
5. Проделать подобную операцию со всеми подобными элементами табличного массива.

Рассмотренный выше метод удаления непечатаемых символов отнимет у пользователя много времени на реализацию.

Важно! Функция «ПЕЧСИМВ()» обычно является аргументом другой функции «СЖПРОБЕЛ()», т.е. два этих оператора используются одновременно, удаляя и лишние пробелы, и непечатаемые знаки из исходного текста.

Список стандартных функций Microsoft Office Excel. Присутствует во всех версиях программного обеспечения

Способ 3. Использование специального макроса

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

1. Зайти в редактор Visual Basic. Для этого необходимо, находясь на рабочем листе Эксель, одновременно с клавиатуры зажать кнопки «Alt+F11». Есть и другие способы запуска редактора, однако они более долгие по выполнению.

1. В верхней колонке инструментов нажать ЛКМ по слову «Insert».
2. В развернувшемся контекстном меню нужно кликнуть по строке «Module».
3. После выполнения предыдущих действий откроется рабочее поле для ввода кода. Сверху открывшегося окна будет прописано текущее название документа Microsoft Office Excel, которое задал для него пользователь ПК.
4. Ввести с клавиатуры код, показанный на изображении ниже.

1. После введения кода понадобится нажать на «Enter» для его запуска.
2. Сохранить программу в своей книге макросов при необходимости, закрыть меню Visual Basic и проверить результат. Если все вышеуказанные действия были сделаны правильно, то в исходном табличном массиве строки с непечатаемыми символами автоматически удалятся.

Дополнительная информация! Для экономии времени рассмотренный выше код программы можно найти в интернете и вставить в окно редактора Visual Basic сочетанием клавиш «Ctrl+V».

Способ 4. Повторное заполнение ячеек

Этот метод актуален, если в одном элементе табличного массива присутствуют непечатаемые символы. Тогда их можно удалить и написать заново, затратив минимальное количество времени на выполнение поставленной задачи. Возможно, при повторном заполнении ячейки проблема устранится, и исходная табличка примет «нормальный» вид. Для полной деинсталляции содержимого ячейки ее достаточно выделить и нажать на кнопку «Backspace» либо «Delete» с клавиатуры компьютера.

Заключение

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

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

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

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

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

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

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

1. Выберите пустую ячейку D2 рядом с C2 и введите: » = ЧИСТЫЙ (C2) ».

2. Нажмите Enter .

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

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

Работы С Нами Kutools for ExcelАвтора Удалить символы, вы можете быстро и легко удалить непечатаемые символы из ячеек.

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

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

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

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

Шаг 4. Нажмите Ok or Применить для удаления непечатаемых символов в диапазоне.

Эта утилита также может удалять числовые символы, нечисловые символы, непечатаемые символы и т. Д. Для получения более подробной информации о Удалить символы, Пожалуйста, посетите Описание функции «Удалить символы».