Как убрать лишние пробелы в Excel?
Рассмотрим способы удаления пробелов в Excel (например, между словами, в начале или конце предложений, в числах).
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Зачастую при копировании текста или переносе таблиц возникают проблемы с наличием лишних символов, которые могут появиться по самым разным причинам: неправильный ввод данных, импортирование данных из внешних источников и т.д.
Достаточно часто лишние пробелы в Excel встречаются в тексте в следующих случаях:
- В начале или в конце предложений;
- Дублирование пробелов;
- Невидимые символы, в т.ч. неразрывный пробел;
- В числах (в разделителях разрядов).
Помимо того, что данные могут стать трудночитаемыми, так и использование обычных инструментов Excel (к примеру, сортировка, фильтрация, поиск, применение функций и т.д.) становится затруднительным и некорректным.
Поэтому, чтобы при работе избежать подобных проблем, для корректной работы необходимо удалять лишние символы.
Как удалить пробелы в Excel?
Рассмотрим 2 основных способа, как можно удалить лишние символы:
- Инструмент «Найти и заменить». Удаление пробелов через диалоговое окно;
- С помощью функций. Формульный вариант удаления.
Инструмент «Найти и заменить»
Предположим, что у нас есть текст или таблица со словами, в котором присутствует дублирование пробелов.
Для начала выделяем диапазон ячеек с данными и выбираем на панели вкладок Главная -> Редактирование -> Найти и выделить -> Заменить либо просто нажимаем сочетание клавиш Ctrl + H:
Появляется стандартное диалоговое окно замены, затем вводим в поле Найти два пробела, а в Заменить на — один. Нажимаем Заменить все и повторяем операцию до тех пор, пока Excel находит повторения.
Как видим алгоритм достаточно простой, однако данный способ удаления пробелов не срабатывает, если они присутствуют в конце или в начале текста.
С помощью функций
В этом случае можно воспользоваться стандартной функцией СЖПРОБЕЛЫ, которая удаляет все пробелы, за исключением одиночных между словами.
Предположим, что у нас имеется список фамилий (столбец A) содержащий лишние пробелы как между словами, так и в начале или конце фамилии. Давайте создадим вспомогательный столбец (столбец B), куда введем формулу СЖПРОБЕЛЫ ссылающуюся на столбец с фамилиями:
Далее копируем содержимое столбца B и вставляем значения без связей в столбец A, после чего удаляем вспомогательный столбец.
Минусом данного способа является его неудобство при работе с большими объемами данных, так как для каждой редактируемой ячейки необходимо создавать дополнительное место где будет обрабатываться исходная ячейка.
Неразрывный пробел в Excel
В случае если после совершенных действий лишние пробелы все же не получилось удалить, то вероятно в тексте могут содержаться неразрывные пробелы.
Данный символ внешне выглядит как обычный пробел, однако его нельзя удалить с помощью функции СЖПРОБЕЛЫ или инструмента «Найти и Заменить» (при вводе в поле для замены обычного пробела).
Как видим в ячейке A2 содержатся лишние пробелы, но функция СЖПРОБЕЛЫ их не удаляет.
Каждый символ в Excel имеет свою кодировку в таблице знаков ANSI (для ОС Windows), в том числе и интересные нам одиночный пробел (символ 32) и неразрывный (символ 160).
Поэтому с помощью функции ПОДСТАВИТЬ заменяем неразрывный пробел (символ 160) на обычный (символ 32), а затем к полученному результату применяя функцию СЖПРОБЕЛЫ получаем требуемый результат:
На самом деле убрать неразрывный пробел можно и с помощью «Найти и заменить», для этого в поле Найти необходимо ввести неразрывный пробел и заменить его либо на обычный, либо удалить, оставив поле Заменить на пустым.
Возникает вопрос, как это сделать?
Первый способ — непосредственно скопировать данный символ из текста и ввести в поле Найти.
Второй способ — воспользоваться Alt-кодом (для систем Windows) и ввести комбинацию Alt + 0160 (удерживая клавишу Alt нужно набрать 0160 на цифровой клавиатуре).
Как убрать пробелы в числах в Excel?
Проблема с удалением пробелов встречается и при работе с числами — в числах попадаются пробелы (к примеру, вместо разделителей разрядов), из-за чего они воспринимаются в Excel как текст.
Предположим, что у нас имеется таблица с числами, где содержатся пробелы. Выделяем диапазон с данными и с помощью инструмента «Найти и Заменить» удаляем пробелы, подставив вместо одиночного пробела пустое поле, как показано в примере выше.
Аналогичный результат можно получить и с помощью формулы:
При этом не забудьте умножить результат на 1, чтобы перевести формулу из текста в число.
Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.
Два способа удаления пробелов и других символов из ячеек в Excel
Иногда при сравнении двух, казалось бы одинаковых, списков при помощи функции ВПР возникают ошибки и функция не находит одинаковые значения. Одной из причин является наличие пробелов между словами в ячейке, причем пробелы могут стоять в любой самой неожиданной части текста и использование функций СЖПРОБЕЛЫ() и ЛЕВСИМВ(), ПРАВСИМВ() не дает результата. Тогда нужно удалить все пробелы из текста. Удаление пробелов можно выполнить несколькими способами. Два из них далее.
Пример таблицы:
| С пробелами. | Без пробелов. |
| Зеленые Яблоки | ЗеленыеЯблоки |
| Спелые Сливы | СпелыеСливы |
| Черная Книга | ЧернаяКнига |
| Матовая Поверхность | МатоваяПоверхность |
| Глянцевая столешница | Глянцеваястолешница |
Первый способ: использование опции замены текста в ячейках.
Для использования выделите диапазон . Нажмите сочетание клавиш ctrl+F . Выберите вкладку «заменить». В поле «Найти» поставьте пробел. В поле «Заменить на» ничего не пишите. Нажмите «Enter». Все пробелы из выделенного диапазона будут удалены.
Второй способ: удаление пробелов при помощи функции подставить.
Функция ПОДСТАВИТЬ() подставляет вместо указанного текста другой текст. Чтобы использовать эту функцию, следует :
- Поставить курсор в пустую ячейку ;
- Выбрать функцию ПОДСТАВИТЬ();
- Указать ячейку, из которой будет обрабатываться текст;
- В поле «Стар_текст» внести пробел в кавычках « »;
- В поле «Нов_текст» внести пустые кавычки «»;
- Нажать «Ok».
Аналогичными способами можно удалять и другие символы.