Как удалить пробелы в ячейках excel перед текстом

Два способа удаления пробелов и других символов из ячеек в Excel

Иногда при сравнении двух, казалось бы одинаковых, списков при помощи функции ВПР возникают ошибки и функция не находит одинаковые значения. Одной из причин является наличие пробелов между словами в ячейке, причем пробелы могут стоять в любой самой неожиданной части текста и использование функций СЖПРОБЕЛЫ() и ЛЕВСИМВ(), ПРАВСИМВ() не дает результата. Тогда нужно удалить все пробелы из текста. Удаление пробелов можно выполнить несколькими способами. Два из них далее.

Пример таблицы:

С пробелами. Без пробелов.
Зеленые Яблоки ЗеленыеЯблоки
Спелые Сливы СпелыеСливы
Черная Книга ЧернаяКнига
Матовая Поверхность МатоваяПоверхность
Глянцевая столешница Глянцеваястолешница

Первый способ: использование опции замены текста в ячейках.Замена пробелов через поиск

Для использования выделите диапазон . Нажмите сочетание клавиш ctrl+F . Выберите вкладку «заменить». В поле «Найти» поставьте пробел. В поле «Заменить на» ничего не пишите. Нажмите «Enter». Все пробелы из выделенного диапазона будут удалены.

Второй способ: удаление пробелов при помощи функции подставить.Удаление пробелов в Эксель

Функция подставить

Функция ПОДСТАВИТЬ() подставляет вместо указанного текста другой текст. Чтобы использовать эту функцию, следует :

  • Поставить курсор в пустую ячейку ;
  • Выбрать функцию ПОДСТАВИТЬ();
  • Указать ячейку, из которой будет обрабатываться текст;
  • В поле «Стар_текст» внести пробел в кавычках « »;
  • В поле «Нов_текст» внести пустые кавычки «»;
  • Нажать «Ok».

Аналогичными способами можно удалять и другие символы.

Как убрать лишние пробелы в 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, чтобы перевести формулу из текста в число.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.

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