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

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

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

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

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

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

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

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

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

Рассмотрим пример. Предположим, что у нас имеется список сотрудников компании и мы хотим отфильтровать только тех сотрудников, у которых фамилии начинаются на конкретную букву (к примеру, на букву «п»):

Для начала добавляем фильтр на таблицу (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или нажимаем сочетание клавиш Ctrl + Shift + L).
Для фильтрации списка воспользуемся символом звездочки, а именно введем в поле для поиска «п*» (т.е. фамилия начинается на букву «п», после чего идет произвольный текст):

Фильтр определил 3 фамилии удовлетворяющих критерию (начинающиеся с буквы «п»), нажимаем ОК и получаем итоговый список из подходящих фамилий:

В общем случае при фильтрации данных мы можем использовать абсолютно любые критерии, никак не ограничивая себя в выборе маски поиска (произвольный текст, различные словоформы, числа и т.д.).
К примеру, чтобы показать все варианты фамилий, которые начинаются на букву «к» и содержат букву «в», то применим фильтр «к*в*» (т.е. фраза начинается на «к», затем идет произвольный текст, потом «в», а затем еще раз произвольный текст).
Или поиск по «п?т*» найдет фамилии с первой буквой «п» и третьей буквой «т» (т.е. фраза начинается на «п», затем идет один произвольный символ, затем «т», и в конце опять произвольный текст).

Применение в функциях

Как уже говорилось выше, подстановочные знаки в Excel могут использоваться в качестве критерия при сравнении текста в различных функциях Excel (например, СЧЁТЕСЛИ , СУММЕСЛИ , СУММЕСЛИМН , ГПР, ВПР и другие).

Повторим задачу из предыдущего примера и подсчитаем количество сотрудников компании, фамилии которых начинаются на букву «п».
Воспользуемся функцией СЧЁТЕСЛИ, которая позволяет посчитать количество ячеек соответствующих указанному критерию.
В качестве диапазона данных укажем диапазон с сотрудниками (A2:A20), а в качестве критерия укажем запись «п*» (т.е. любая фраза начинающаяся на букву «п»):

Как и в первом примере, в результате мы получили ровно 3 фамилии.

Однако не все функции поддерживают применение подстановочных знаков. Некоторые из них (к примеру, функция НАЙТИ) любой символ воспринимают как текст, даже несмотря на то, что он может быть служебным.
С помощью функции НАЙТИ найдем в тексте позицию вхождения вопросительного знака и звездочки:

Обратным примером служит аналогичная функция ПОИСК, в которой мы должно четко указать что ищем именно служебный символ:

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

Удалить символы в начале ячейки (слева)

Формулы выше требуют создания дополнительного столбца. Чтобы сэкономить время, если исходные данные не нужны, мной были разработаны функции !SEMTools , удаляющие символы прямо на месте.

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

Удалить символы в конце ячейки (справа):

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

Выделите диапазон, на панели !SEMTools в меню УДАЛИТЬ -> Символы -> обрезать выберите «с начала ячейки». В появившемся диалоговом окошке вбейте количество символов, которое хотите удалить, и нажмите ОК.

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

Зачастую текст, который достается нам для работы в ячейках листа 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 подчиняется ряду правил. Быстрее всего устранить проблему можно с помощью специальной формулы. Способы деинсталляции лишних знаков были рассмотрены выше. Их рекомендуется внимательно изучить для полного понимания темы.