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

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

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

6 ответов

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

заменит любой разрыв строки символом слова для разрыва строки. И вложенная формула заменит бота, пробел и enter. (Примечание: Для того, чтобы ввести “Enter” в Формуле, вам нужно нажать Alt+Enter при редактировании формулы.

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

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

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

CTRL+H заменить все пробелы на

Это поможет быстро для пространств без программирования, а для обратного просто замените

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

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

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

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

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

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

2 Do Заменить Все и введите “[s-p-a-c-e]”

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

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

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

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

Источник: kompsekret.ru

Как отображать или просматривать непечатаемые символы в Excel?

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

6 ответов

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

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

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

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

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

CTRL + H заменяет все пробелы на

Это поможет быстро для пробелов без программирования, а наоборот – просто заменить

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

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

Не точно отвечает на ваш вопрос, но я задал формат номера:

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

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

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

2 Do Заменить все и введите «[s-p-a-c-e]»

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

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

Читайте также:  Как в эксель создать таблицу

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

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

Источник: ubuntugeeks.com

Microsoft Excel

трюки • приёмы • решения

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

В Excel есть окно Символ, которое применяется для поиска и вставки специальных символов в ячейку (рис. 66.1). Вы можете открыть это окно, выбрав команду Вставка ► Символы ► Символ.

На вкладке Символы в раскрывающемся списке Шрифт выберите нужный шрифт. Для большинства шрифтов вы также можете выбирать категорию шрифтов из раскрывающегося списка Набор. Выберите нужный символ и нажмите кнопку Вставить. Продолжите вставку дополнительных символов, если они вам еще нужны, или нажмите кнопку Закрыть, чтобы закрыть окно.

Рис. 66.1. Символы из категории технические знаки шрифта Arial Unicode MS

Если вы вставили символ из определенного шрифта, то Excel продолжит отображать тот же символ независимо оттого, какой шрифт применился к ячейке. Для большого набора символов используйте шрифт Arial Unicode MS.

Если вы используете какой-либо символ часто, то можете захотеть сделать его более доступным, например это может быть КНС. В Excel это выполняется с помощью функции Автозамена. Исполнив следующие инструкции, вы сделаете нужный вам символ (для нашего примера он выбран на рис. 66.1) легкодоступным.

  1. Выберите пустую ячейку.
  2. Выполните команду Вставка ► Символы ► Символ и используйте диалоговое окно. Символ для поиска символов, которые вы хотите использовать. В нашем примере код символа равен 2318, а сам символ относится к категории технические знаки шрифта Arial Unicode MS.
  3. Вставьте этот символ в ячейку, нажав кнопку Вставить.
  4. Нажмите Закрыть, чтобы закрыть диалоговое окно Символ.
  5. Нажмите Ctrl+C, чтобы скопировать символ в активной ячейке.
  6. Выберите Файл ► Параметры, чтобы открыть окно Параметры Excel, перейдите в раздел Правописание, а затем нажмите кнопку Параметры автозамены для вызова диалогового окна Автозамена (или просто нажмите Alt+TA).
  7. В окне Автозамена выберите одноименную вкладку.
  8. В поле заменять введите последовательность символов, например (р).
  9. Перейдите к полю на и нажмите Ctrl+V, чтобы вставить специальный символ.
  10. Нажмите для закрытия окна Автозамена.

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

Источник: excelexpert.ru

Подстановочные знаки (символы *, ? и

Рассмотрим применение подстановочных знаков в Excel (символы звездочки «*», тильды «

» и вопросительного знака «?») и их использование при поиске и замене текстовых значений.

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

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

  • * (звездочка); Обозначает любое произвольное количество символов. Например, поиск по фразе «*ник» найдет слова типа «понедельник», «всадник», «источник» и т.д.
  • ? (вопросительный знак); Обозначает один произвольный символ. К примеру, поиск по фразе «ст?л» найдет «стол», «стул» и т.д.

(тильда) с последующими знаками *, ? или

. Обозначает конкретный символ *, ? или

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

» и искать по фразе «хор

» гарантирует, что Excel прочитает следующий символ как текст, а не как подстановочный знак.

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

Фильтрация данных

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


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


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


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

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

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

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


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

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


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


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

Инструмент «Найти и заменить»

Подстановочные знаки в Excel также можно использовать для поиска и замены текстовых значений в инструменте «Найти и заменить» (комбинация клавиш Ctrl + F для поиска и Ctrl + H для замены).

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

Чтобы несколько раз не искать данные по словам «молоко» или «малоко», при поиске воспользуемся критерием «м?локо» (т.е. вторая буква — произвольная):


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

Как заменить звездочку «*» в Excel?

Практически наверняка каждый сталкивался со следующей ситуацией — в тексте присутствует символ звездочки, который необходимо удалить или заменить на какой-либо другой текст.
Однако при попытке заменить звездочку возникают трудности — при замене меняются абсолютно весь текст, что естественно и логично, так как Excel воспринимает символ «*» как любой произвольный текст.
Но мы теперь уже знаем как с этим бороться, поэтому в поле Найти указываем текст «

(явно показываем, что звездочка является специальным символом), а в поле Заменить на указываем на что заменяем звездочку, либо оставляем поле пустым, если хотим удалить звездочку:


Аналогичная ситуация и при замене или удалении вопросительного знака и тильды.
Производя замену «

(для тильды — «

») мы также без проблем сможем заменить или удалить спецсимвол.

Источник: tutorexcel.ru

30 функций Excel за 30 дней: ПЕЧСИМВ (CLEAN)

Вчера в марафоне 30 функций Excel за 30 дней мы перемещались по всей рабочей книге Excel, открывали файлы Excel и веб-сайты, и все это благодаря функции HYPERLINK (ГИПЕРССЫЛКА).

В 29-й день марафона мы будем изучать функцию CLEAN (ПЕЧСИМВ). Иногда данные, которые мы загружаем с веб-сайтов или извлекаем из загруженных файлов, содержат не желательные символы, и функция CLEAN (ПЕЧСИМВ) позволяет их удалить. Она не сделает за Вас всю тяжёлую работу и не поможет справиться с беспорядком, который оставляют дети. Эта функция идеально подходит для ленивого воскресенья!

Итак, давайте внимательно посмотрим информацию по функции CLEAN (ПЕЧСИМВ) и изучим примеры её работы. Если у Вас есть дополнительная информация или примеры по этой функции, пожалуйста, делитесь ими в комментариях.

Функция 29: CLEAN (ПЕЧСИМВ)

Функция CLEAN (ПЕЧСИМВ) удаляет некоторые непечатаемые символы из текста – символы с кодами от 0 до 31, 129, 141, 143, 144 и 157.

Как можно использовать функцию CLEAN (ПЕЧСИМВ)?

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

  • Удалить некоторые непечатаемые символы.
  • Заменить непечатаемые символы в тексте.

Синтаксис CLEAN (ПЕЧСИМВ)

Функция CLEAN (ПЕЧСИМВ) имеет вот такой синтаксис:

CLEAN(text)
ПЕЧСИМВ(текст)

  • text (текст) – любая информация, в которой требуется удалить непечатаемые символы.

Ловушки CLEAN (ПЕЧСИМВ)

Функция CLEAN (ПЕЧСИМВ) удаляет только некоторые непечатаемые символы из текста – символы с кодом от 0 до 31, 129, 141, 143, 144 и 157. Для других непечатаемых символов, таких как символ неразрывного пробела с кодом 160, можно использовать функцию SUBSTITUTE (ПОДСТАВИТЬ), чтобы заменить их символами пробела или пустыми строками.

Пример 1: Удаляем непечатаемые символы

Функция CLEAN (ПЕЧСИМВ) удаляет некоторые непечатаемые символы, к примеру, символы с кодами от 0 до 30 по таблице ASCII. В этом примере мы добавим символы 9 и 13 к исходному тексту в ячейке C3.

Функция LEN (ДЛСТР) показывает, что количество символов в ячейке C5 возросло до 15, считая непечатаемые символы.

Функция CLEAN (ПЕЧСИМВ) в ячейке C7 удаляет эти символы, и общее количество символов уменьшается на 2, возвращаясь к исходному значению 13.

Пример 2: Заменяем непечатаемые символы

В ситуации, когда функция CLEAN (ПЕЧСИМВ) не может удалить определенные символы, например, символы с кодами 127 и 160, Вы можете использовать функцию SUBSTITUTE (ПОДСТАВИТЬ), чтобы заменить их.

Источник: office-guru.ru

Сводные таблицы 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) и введите туда его текст.

Sub Apostrophe_Remove() For Each cell In Selection If Not cell.HasFormula Then v = cell.Value cell.Clear cell.Formula = v End If Next End Sub

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

Источник: pivot-table.ru