Очистка текста от пробелов в Excel: 3 рабочих способа

Чтобы удалить лишние пробелы в Excel, используйте функцию СЖПРОБЕЛЫ для стандартной очистки (оставляет по одному пробелу между словами) или инструмент «Найти и заменить» (Ctrl+H) для полного удаления всех пробелов. Если данные содержат неразрывные пробелы (код 160), предварительно замените их на обычные с помощью функции ПОДСТАВИТЬ или комбинации клавиш Alt+0160. Для обработки больших массивов данных оптимально использовать Power Query.

Лишние пробелы нарушают работу функций ВПР (VLOOKUP), сводных таблиц и сортировки. Выбор метода зависит от того, нужно ли вам сохранить структуру текста (слова через пробел) или убрать все разделители полностью (например, в телефонах или ИНН).

Типы пробелов: почему СЖПРОБЕЛЫ не всегда помогает

Excel различает два вида пробельных символов, которые визуально выглядят одинаково, но имеют разные коды:

Если функция СЖПРОБЕЛЫ не убирает пробелы, скорее всего, вы имеете дело с символом код 160. Перед очисткой его необходимо преобразовать в обычный пробел.

Способ 1: Функция СЖПРОБЕЛЫ (стандартная очистка)

Функция СЖПРОБЕЛЫ (англ. TRIM) удаляет все пробелы, кроме одиночных между словами. Это лучший выбор для исправления ФИО, адресов и названий товаров.

Синтаксис

=СЖПРОБЕЛЫ(текст)

Алгоритм действий

1. В соседнем столбце введите формулу: =СЖПРОБЕЛЫ(A2).
2. Протяните формулу вниз до конца таблицы.
3. Скопируйте полученный столбец (Ctrl+C).
4. Вставьте значения поверх исходных данных: ПКМ → Специальная вставка → Значения (или Ctrl+Alt+V → выбрать «Значения»).
5. Удалите вспомогательный столбец.

Если есть неразрывные пробелы

Оберните функцию в ПОДСТАВИТЬ, чтобы сначала заменить код 160 на код 32:

=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A2; СИМВОЛ(160); " "))

Способ 2: Найти и заменить (полное удаление)

Инструмент «Найти и заменить» подходит, если нужно удалить абсолютно все пробелы (например, превратить 8 900 123-45-67 в 89001234567).

Быстрое удаление всех пробелов

1. Выделите нужный диапазон ячеек.
2. Нажмите Ctrl+H.
3. В поле Найти: поставьте один пробел.
4. В поле Заменить на: оставьте пустым.
5. Нажмите Заменить все.

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

Если нужно просто схлопнуть множественные пробелы до одного, но не использовать формулы:

1. Нажмите Ctrl+H.
2. Найти: два пробела.
3. Заменить на: один пробел.
4. Нажимайте «Заменить все» несколько раз, пока Excel не сообщит, что замен выполнено: 0.

Замена неразрывных пробелов через меню

1. Скопируйте неразрывный пробел из ячейки с данными (выделите его мышкой или используйте Alt+0160 на нумпаде в любой ячейке).
2. Откройте Ctrl+H.
3. Вставьте скопированный символ в поле Найти.
4. В поле Заменить на поставьте обычный пробел (или оставьте пустым, если нужно удалить).
5. Нажмите Заменить все.

Способ 3: Power Query для больших данных

Power Query идеален для регулярной очистки тысяч строк без использования вспомогательных столбцов и макросов.

Базовая обрезка (Trim)

Удаляет пробелы только в начале и конце строки.

1. Выделите таблицу → вкладка Данные → Из таблицы/диапазона.
2. В редакторе Power Query выберите столбец.
3. Вкладка Преобразование → Формат → Удалить пробелы (Trim).
4. Главная → Закрыть и загрузить.

Полная очистка (аналог СЖПРОБЕЛЫ + удаление кода 160)

Для удаления лишних пробелов внутри текста и неразрывных пробелов используйте пользовательский столбец.

1. В редакторе Power Query: Добавить столбец → Настраиваемый столбец.
2. Введите имя (например, CleanText) и формулу M:

Text.Combine(List.Select(Text.SplitAny([ИсходныйСтолбец], "#(0020)#(00A0)"), each _ <> ""), " ")

Где [ИсходныйСтолбец] — название вашего столбца с данными. 3. Нажмите ОК. 4. Удалите старый столбец, переименуйте новый при необходимости. 5. Загрузите данные обратно в Excel.

Сравнение методов

Частые ошибки

1. Формула не видит пробелы. Причина: Это неразрывные пробелы (код 160). Решение: Используйте конструкцию СЖПРОБЕЛЫ(ПОДСТАВИТЬ(...; СИМВОЛ(160); " ")).

2. После замены сломались формулы. Причина: Вы выделили весь лист (Ctrl+A) вместо конкретного диапазона и удалили пробелы внутри формул других ячеек. Решение: Всегда выделяйте только целевой диапазон данных перед нажатием «Заменить все».

3. Данные не обновляются в Power Query. Причина: Вы изменили исходную таблицу, но не обновили запрос. Решение: Нажмите Данные → Обновить всё (или ПКМ по таблице результата → Обновить).

FAQ

Как проверить, какой именно пробел в ячейке? Используйте формулу =КОДСИМВ(ПРАВСИМВ(A1;1)). Если результат 32 — пробел обычный, если 160 — неразрывный.

Можно ли удалить пробелы макросом VBA? Да, но использование встроенных функций или Power Query безопаснее и проще в поддержке. Макросы требуют сохранения файла в формате .xlsm и могут блокироваться политиками безопасности компании.

Что делать, если пробелы остаются после всех манипуляций? Проверьте наличие других непечатаемых символов (например, табуляции). В Power Query используйте функцию Text.Clean(), которая удаляет первые 32 непечатаемых символа ASCII, а затем применяйте очистку пробелов.