Как увидеть пробелы в excel

Функция СЖПРОБЕЛЫ

В этой статье описаны синтаксис формулы и использование функции СЖПРОБЕЛЫ в Microsoft Excel.

Описание

Удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать лишние пробелы.

Важно: Функция СЖПРОБЕЛЫ предназначена для удаления из текста знаков пробела 7-разрядного кода ASCII (значение 32). В наборе знаков Юникода существует дополнительный знак пробела, который называется знаком неразрывного пробела и имеет десятичное значение 160. Этот знак обычно используется на веб-страницах как сущность HTML . Сама по себе функция СЖПРОБЕЛЫ не удаляет этот знак неразрывного пробела. Пример обрезки обоих пробелов из текста см. в десяти лучших способах очистки данных.

Синтаксис

Аргументы функции СЖПРОБЕЛЫ описаны ниже.

Текст Обязательный. Текст, из которого удаляются пробелы.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

=СЖПРОБЕЛЫ(" Доход за первый квартал ")

Удаляет начальные и конечные пробелы из текста в формуле ("Доход за первый квартал")

Как в офисе.

Типичная ошибка, которая встречается в электронных таблицах, связана с невидимыми символами пробелов. [1] В ячейке В2 (рис. 1) находится формула, берущая название цвета в ячейке В1 и возвращающая соответствующий код из таблицы D1:E6: =ВПР(B1;D1:E6;2;ЛОЖЬ).

%d1%80%d0%b8%d1%81-1-%d1%84%d0%be%d1%80%d0%bc%d1%83%d0%bb%d0%b0-%d0%bf%d0%be%d0%b8%d1%81%d0%ba%d0%b0-%d0%b2%d0%be%d0%b7%d0%b2%d1%80%d0%b0%d1%89%d0%b0%d0%b5%d1%82-%d1%86%d0%b2%d0%b5%d1%82-%d0%b2

Рис. 1. Формула поиска возвращает цвет, введенный в ячейку В1

Скачать заметку в формате Word или pdf, примеры в формате Excel

В следующем примере формула в ячейке В2 возвращает ошибку, указывая, что значение Красный не было найдено в таблице. Сотни и тысячи пользователей Excel потратили массу времени, чтобы понять, почему подобные операции не работают. Ответ прост: в ячейке D5 написано не «Красный», а «Красный », то есть за словом следует пробел. Для Excel это две разные последовательности символов.

%d1%80%d0%b8%d1%81-2-%d1%84%d0%be%d1%80%d0%bc%d1%83%d0%bb%d0%b0-%d0%bf%d0%be%d0%b8%d1%81%d0%ba%d0%b0-%d0%bd%d0%b5-%d0%bc%d0%be%d0%b6%d0%b5%d1%82-%d0%bd%d0%b0%d0%b9%d1%82%d0%b8-%d0%b2-%d1%82%d0%b0

Рис. 2. Формула поиска не может найти в таблице слово «Красный»

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

  • один ведущий пробел и более;
  • один завершающий пробел и более;
  • два и более последовательных пробела в самом тексте.

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

  1. Выделите все ячейки с текстом, в которых хотите применить условное форматирование.
  2. Выполните команду Главная —>Условноеформатирование —>Создатьправило, чтобы открыть диалоговое окно Созданиеправилаформатирования (рис. 3).
  3. В верхней части этого окна выберите параметр Использовать формулу для определения форматируемых ячеек.
  4. В области Измените описание правила введите формулу: =D2<>СЖПРОБЕЛЫ(D2). Данная формула предполагает, что ячейка D2 является верхней левой ячейкой в диапазоне. Если это не так, замените адрес верхней левой ячейки, который вы указали в шаге 1.
  5. Нажмите кнопку Формат, чтобы отобразить диалоговое окно Формат ячеек, и выберите тип форматирования, которое хотите применить к ячейкам, содержащим лишние пробелы, — например, желтую заливку.
  6. Нажмите Ok два раза.

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

%d1%80%d0%b8%d1%81-3-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-%d1%83%d1%81%d0%bb%d0%be%d0%b2%d0%bd%d0%be%d0%b3%d0%be-%d1%84%d0%be%d1%80%d0%bc%d0%b0%d1%82%d0%b8

Рис. 3. Параметры условного форматирования для выделения ячеек, содержащих лишние пробелы

Рис. 4. Условное форматирование выделило ячейки с лишними пробелами

Функция СЖПРОБЕЛЫ действует так, что формула, описанная в шаге 4, также применяет условное форматирование ко всем числовым ячейкам. Если в вашем диапазоне встречаются числа, используйте на шаге 4 формулу: =ЕСЛИ(НЕ(ЕНЕТЕКСТ(D2));D2<>СЖПРОБЕЛЫ(D2)). Кроме того, нужно иметь в виду, что функция СЖПРОБЕЛЫ не удаляет (и не воспринимает при условном форматировании) знак неразрывного пробела.

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. — СПб.: Питер, 2014. — С. 183–185.

Ссылка на основную публикацию