Быстрая очистка столбца от лишних пробелов в Excel

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

Лишние пробелы часто появляются при копировании данных из интернета или выгрузке из баз данных. Они мешают корректной работе формул ВПР (VLOOKUP), сводных таблиц и сортировки. Ниже приведены 5 проверенных способов решения этой задачи для версий Excel 2016–2024 и Microsoft 365.

Способ 1: Функция СЖПРОБЕЛЫ (TRIM) — основной метод

Этот способ идеален, когда нужно сохранить структуру текста (слова разделены одним пробелом), но убрать «мусор» по краям и лишние промежутки.

1. Вставьте пустой столбец рядом с загрязненными данными (например, столбец B рядом с A).
2. В ячейку B1 введите формулу:
   • Для русской версии: =СЖПРОБЕЛЫ(A1)
   • Для английской версии: =TRIM(A1)
3. Протяните формулу вниз до конца таблицы (двойной клик по правому нижнему углу ячейки).
4. Выделите полученный столбец, скопируйте его (Ctrl+C).
5. Кликните правой кнопкой мыши по исходному столбцу (A) и выберите «Специальная вставка» → «Значения» (иконка с цифрами 123).
6. Удалите вспомогательный столбец.

Способ 2: Полное удаление пробелов через ПОДСТАВИТЬ

Используйте этот метод, если пробелы не нужны вовсе (например, в номерах кредитных карт, ИНН или артикулах товаров).

Формула заменяет каждый найденный пробел на пустоту: =ПОДСТАВИТЬ(A1; " "; "")

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

1. Введите формулу в соседнюю ячейку.
2. Протяните вниз.
3. Скопируйте результат и вставьте как значения поверх исходных данных.

Для сложных случаев можно комбинировать функции, чтобы сначала убрать лишнее, а затем всё остальное: =СЖПРОБЕЛЫ(ПОДСТАВИТЬ(A1; " "; "")) — эта конструкция сначала сожмет пробелы, а потом удалит оставшиеся (хотя логичнее использовать просто ПОДСТАВИТЬ для полного удаления).

Способ 3: Инструмент «Найти и заменить»

Самый быстрый метод для небольших таблиц, не требующий создания формул.

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

Способ 4: Обработка больших массивов в Power Query

Если у вас таблица на сотни тысяч строк и данные обновляются регулярно, лучше настроить автоматическую очистку через Power Query (доступно в Excel 2016 и новее).

1. Выделите таблицу и перейдите на вкладку «Данные» → «Из таблицы/диапазона».
2. В открывшемся редакторе выделите нужный столбец.
3. На вкладке «Преобразование» выберите «Формат» → «Обрезка» (Trim) — уберет крайние пробелы.
4. Там же выберите «Очистка» (Clean) — уберет непечатаемые символы.
5. Чтобы убрать все пробелы внутри текста: «Заменить значения» → Найти: (пробел), Заменить на: (пусто).
6. Нажмите «Закрыть и загрузить». Данные выгрузятся на новый лист уже очищенными.

Способ 5: Макрос VBA для автоматизации

Для пользователей, которым приходится чистить данные ежедневно, подойдет макрос. Он работает мгновенно даже на больших объемах.

1. Нажмите Alt+F11, чтобы открыть редактор VBA.
2. В меню выберите Insert → Module.
3. Вставьте следующий код:

Sub CleanSpaces()
    Dim rng As Range
    On Error Resume Next
    Set rng = Application.InputBox("Выберите диапазон для очистки:", Type:=8)
    If rng Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    For Each cell In rng
        If Not IsError(cell.Value) Then
            ' Удаляет ведущие, конечные и лишние внутренние пробелы
            cell.Value = Application.WorksheetFunction.Trim(cell.Value)
        End If
    Next cell
    Application.ScreenUpdating = True
    MsgBox "Очистка завершена!", vbInformation
End Sub

4. Закройте редактор, нажмите Alt+F8, выберите макрос CleanSpaces и нажмите «Выполнить».
5. Выделите мышкой нужный диапазон в ответ на запрос системы.

Сравнение методов обработки данных

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

• Неразрывные пробелы. Иногда данные содержат специальный символ CHAR(160) (неразрывный пробел), который обычные методы не видят. Перед основной очисткой прогоните данные через формулу: =ПОДСТАВИТЬ(A1; СИМВОЛ(160); " ").
• Числа становятся текстом. После использования текстовых функций результаты могут восприниматься как текст. Если нужно вернуть числовой формат, умножьте результат на 1 или используйте «Текст по столбцам».
• Табуляция. Символ табуляции (CHAR(9)) тоже считается пробельным символом. Функция СЖПРОБЕЛЫ убирает его, но обычный «Найти и заменить» может пропустить, если искать только обычный пробел.

FAQ

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

Вопрос: Можно ли убрать пробелы во всем файле сразу? Ответ: Да, выделите все ячейки (Ctrl+A), нажмите Ctrl+H и замените пробел на пустоту. Но помните о риске склеивания слов в предложениях. Безопаснее делать это по конкретным столбцам.

Вопрос: Как проверить, остались ли пробелы? Ответ: Используйте формулу =ДЛСТР(A1). Сравните длину ячейки до и после очистки. Также можно подсветить ячейки с пробелами через условное форматирование с формулой =ИСТИНА, если найти пробел функцией ПОИСК.