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

Чтобы удалить пробелы в числах в Excel и превратить их в полноценные числа для расчетов, используйте функцию =ЗНАЧЕН(ПОДСТАВИТЬ(A1;" ";"")) или инструмент «Найти и заменить» (Ctrl+H). Это устраняет лишние символы, включая неразрывные пробелы, и позволяет корректно сортировать, фильтровать и суммировать данные.

Ниже — подробные инструкции для разных ситуаций: от простой замены до автоматизации через макросы.

Почему Excel не видит числа

Часто при импорте данных из 1С, банковских выписок или веб-страниц числа сохраняются как текст. Признаки проблемы:

• Числа выровнены по левому краю ячейки.
• В левом верхнем углу ячейки появляется зеленый треугольник.
• Функция СУММ игнорирует эти ячейки или возвращает 0.
• При попытке умножить или сложить такие ячейки возникает ошибка.

Главная причина — наличие лишних пробелов (обычных или неразрывных  ) и текстового формата ячейки.

Способ 1: Найти и заменить

Этот метод подходит, если нужно быстро очистить диапазон без создания дополнительных столбцов.

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

Как убрать неразрывные пробелы:

1. Скопируйте неразрывный пробел из любой ячейки с проблемой (выделите его в строке формул и нажмите Ctrl+C).
2. Вставьте его в поле Найти в окне замены (Ctrl+V).
3. Нажмите Заменить все.

Способ 2: Формулы для очистки

Если данные нужно сохранить в исходном виде, а очищенный вариант получить в новом столбце, используйте формулы.

Базовая очистка

Для удаления обычных пробелов и преобразования текста в число:

=ЗНАЧЕН(ПОДСТАВИТЬ(A1; " "; ""))

• ПОДСТАВИТЬ удаляет все пробелы.
• ЗНАЧЕН (или VALUE) превращает полученную строку в число.

Глубокая очистка (пробелы + неразрывные пробелы)

Если в данных смешаны разные типы пробелов:

=ЗНАЧЕН(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1; СИМВОЛ(160); ""); " "; ""))

• СИМВОЛ(160) — это код неразрывного пробела.
• Сначала убираем неразрывные, затем обычные.

Использование СЖПРОБЕЛЫ

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

=ЗНАЧЕН(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1); " "; ""))

После применения формулы не забудьте скопировать результат и вставить его как Значения (Специальная вставка → Значения), чтобы избавиться от зависимостей.

Способ 3: Умное преобразование без формул

Если вам не нужны формулы, можно принудительно заставить Excel пересчитать данные.

Метод «Текст по столбцам»

1. Выделите столбец с проблемными числами.
2. Перейдите на вкладку Данные → Текст по столбцам.
3. Нажмите Готово (ничего не меняя в настройках).

Excel принудительно перезапишет данные, распознав их тип. Это часто исправляет формат «текст» на «число» и убирает невидимые символы.

Метод «Умножение на 1»

1. В любую пустую ячейку введите число 1.
2. Скопируйте эту ячейку (Ctrl+C).
3. Выделите диапазон с проблемными числами.
4. Нажмите правой кнопкой мыши → Специальная вставка.
5. Выберите операцию Умножить и нажмите ОК.

Все выделенные ячейки будут умножены на 1, что автоматически конвертирует текстовые значения в числа.

Способ 4: Макрос VBA

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

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

Sub CleanNumbers()
    Dim cell As Range
    Dim cleanValue As String
    
    Application.ScreenUpdating = False
    
    For Each cell In Selection
        If Not IsEmpty(cell) Then
            ' Заменяем неразрывный пробел (Chr(160)) и обычный пробел на пустоту
            cleanValue = Replace(Replace(cell.Value, Chr(160), ""), " ", "")
            
            ' Проверяем, является ли результат числом
            If IsNumeric(cleanValue) Then
                cell.Value = CDbl(cleanValue)
                cell.NumberFormat = "General" ' Или нужный формат, например "#,##0.00"
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

4. Закройте редактор.
5. Выделите нужные ячейки в Excel.
6. Нажмите Alt + F8, выберите CleanNumbers и нажмите Выполнить.

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

FAQ

Вопрос: Как удалить пробелы только в конце или начале числа?
Ответ: Используйте функцию СЖПРОБЕЛЫ(A1). Она уберет ведущие и замыкающие пробелы, но оставит внутренние. Если нужно убрать все — используйте ПОДСТАВИТЬ.

Вопрос: Почему после удаления пробелов сумма всё равно не считается?
Ответ: Скорее всего, ячейки остались в текстовом формате. Примените метод «Текст по столбцам» или умножение на 1, чтобы принудительно изменить тип данных.

Вопрос: Можно ли удалить пробелы во всем файле сразу?
Ответ: Да, выделите все листы (удерживая Shift, кликните по ярлычкам листов) и выполните «Найти и заменить». Но будьте осторожны: это может затронуть текстовые данные, где пробелы нужны (например, ФИО). Лучше обрабатывать только числовые столбцы.

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