Удаление лишних пробелов в файлах Excel

Лишние пробелы в ячейках Excel не только влияют на внешний вид таблиц, но и создают проблемы при работе с данными на листах с использованием встроенных функций Excel, формул или макросов. Несмотря на то, что пробел является невидимым символом, значения ячеек при наличии (или отсутствии) пробелов отличаются друг от друга. Это влияет на сравнение значений ячеек между собой, что затрудняет или делает невозможным, например построение сводных таблиц или поиск дубликатов. В MS Excel есть встроенная функция СЖПРОБЕЛЫ, которая позволяет удалять пробелы после последнего слова или перед первым словом, а также избыточные пробелы между словами. Однако её использование требует создания нового столбца и удаления исходного. Другие метода, такие как поиск и замена (например, двух пробелов на один) не способны заменить лишние пробелы в начале и конце слов или предложений. Кроме того, все эти методы требуют большого объема ручного труда. Данное приложение позволяет убрать лишние пробелы в каждой ячейке одновременно для неограниченного количества листов и файлов в начале и конце строк, между словами, символами пунктуации, а также удалять неразрывные пробелы (NBSP) и служебные символы переноса строки.

Купить

Пожизненная лицензия

Бесплатные обновления в течении 2 лет

Бесплатная пожизненная поддержка

Скачать приложение

Для покупки на allsoft.by и allsoft.kz перейдите в раздел Купить

Удаление лишних пробелов средствами Excel. Возможности и ограничения

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

Для удаления всех пробелов в начале и конце строки необходимо сделать следующее:

Выберите диапазон ячеек, где необходимо удалить пробелы.

Нажмите правой кнопкой мыши на выделенном диапазоне ячеек и выберите "Формат ячеек".

В диалоговом окне "Формат ячеек" выберите "Текст" в категории "Число".

Нажмите "ОК", чтобы закрыть диалоговое окно.

Нажмите Ctrl+H на клавиатуре, чтобы вызвать диалоговое окно "Найти и заменить".

Введите пробелы в поле "Найти". Например, два пробела.

Введите ничего в поле "Заменить на".

Нажмите на кнопку "Заменить все".

Нажмите "ОК", чтобы закрыть диалоговое окно.

Примечание: Пункты 2-4 необходимы с той целью, чтобы убедиться, что выбранный диапазон ячеек отформатирован как текст. Это важно потому, что Excel может автоматически удалить ведущие нули или преобразовать значения текстовых ячеек в числа в зависимости от формата данных, что может привести к ошибкам при удалении пробелов.
Однако, при работе только с текстовыми ячейками не требуется выполнять данные пункты. Также, можно проигнорировать пункт 3, выбрав вместо этого опцию "Text" (Текст) в меню формата данных.
В итоге, при обработке только текстовых ячеек, можно пропустить пункты 2-4 и следовать напрямую пунктам удаления пробелов.

Для удаления лишних пробелов в середине строки можно использовать следующие действия:

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

Нажмите правой кнопкой мыши на выделенном диапазоне ячеек и выберите "Формат ячеек".

В диалоговом окне "Формат ячеек" выберите "Текст" в категории "Число".

Нажмите "ОК", чтобы закрыть диалоговое окно.

Нажмите Ctrl+H на клавиатуре, чтобы вызвать диалоговое окно "Найти и заменить".

Введите пробелы, которые нужно удалить в поле "Найти". Например, два пробела.

Введите один пробел в поле "Заменить на".

Нажмите на кнопку "Заменить все".

Нажмите "ОК", чтобы закрыть диалоговое окно.

Стоит заметить, что Excel не удаляет пробелы перед символами пунктуации и другими знаками, которые располагаются перед словами. В случае, когда необходимо обработать большое количество данных, рекомендуется использовать специальные программы, такие как XLSTools: Удаление Лишних Пробелов.

Примеры удаления лишних пробелов

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

Пример удаления пробелов в начале и конце строки (то есть с обеих сторон строки),

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

Удаление лишних пробелов между словами (два и более лишних пробела заменяются одним)

Помимо обычных лишних или ненужных пробелов в ячейках таблиц могут быть так называемые неразрывные пробелы (non-breaking space - NBSP). В отличие от обычных, эти пробелы не позволяют переносить слова на другую строку. Приложение позволяет как просто удалять эти пробелы (в этом случае между словами не будет пробелов), так и заменять их обычными, в том числе с возможность удаления избыточных пробелов. В данном примере неразрывные пробелы заменяются на обычные.

Приложение может не только удалять пробелы, но и служебные символы переноса строк, в том числе с возможностью замены на любые произвольные символы или строки. В данном примере перенос строки заменяется на символ пробела.

Удаление лишних пробелов - запуск мастера приложения

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

Итоговая папка для файлов Excel с удалёнными избыточными пробелами

При использовании для удаления пробелов макросов VBA или надстроек для Excel, как правило необходимо создавать резервные копии исходных файлов, особенно если файлов одновременно обрабатывается много. При использовании данного приложения в этом нет необходимости. Программа создаёт копии исходных файлов в заданной папке и обрабатывает именно эти файлы. Интерфейс диалогового окна позволяет изменить базовую папку (по умолчанию это папка "Документы"), а также определить настройки создания дочерней подпапки (1), и при необходимости задать её имя (2).

Итоговая папка для файлов Excel после удаления избыточных (лишних) пробелов

Выбор Excel таблиц для удаления пробелов

Одной из ключевых возможностей приложения является удаление избыточных (лишних) пробелов не на одном листе рабочей книги Excel, а сразу во множестве листов и файлов. Кроме того, в качестве источника файлов можно выбрать папку. В этом случае программа создаст список файлов из всех электронных таблиц, найденных в этой папке, включая подпапки (эта возможность определяется настройками приложения) и сохранит файлы в итоговой папке после обработки с сохранением структуры каталогов. Установите переключатель (1) или (2) и нажмите кнопку (3).

Выбор файлов таблиц Excel для удаления пробелов

Настройка дополнительных параметров

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

Дополнительные параметры - начало диапазона обработки

Параметры и настройки удаления

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

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

Для удаления пробелов в файле примера выполните следующее:

Установите флажок напротив "Вырезать начальные и конечные пробелы" (1) для удаления всех пробелов в начале и в конце строк.

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

Установите флажок напротив "Вырезать пробелы до символов пунктуации" (3) для удаления пробелов до символов точки, запятой, точки с запятой и вопросительного и восклицательного знаков. Установка этого параметра возможна при установке флажка (2).

Нажмите кнопку “Далее” для начала процесса.

Результат выполнения программы после удаления пробелов отображен в открытом в MS Excel файле примера.

Смотрите пошаговое руководство по удалению лишних пробелов в таблицах Excel в видео формате на YouTube:

Файлы примера

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

Скачать ZIP архив

Полезные ссылки:

НАВИГАЦИЯ

ПОДПИСЫВАЙТЕСЬ НА НАС

КОНТАКТЫ