Убираем пробелы в числах Excel: рабочие методы для любого формата

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

Почему Excel считает числа текстом

Если ячейка выровнена по левому краю, а в углу горит зеленый треугольник с восклицательным знаком — это текст. Даже один лишний пробел (в начале, конце или внутри числа, например 1 000) ломает математические операции.

Основные причины:

• Импорт данных из банковских выписок (CSV) или 1С.
• Копирование с веб-сайтов, где используются неразрывные пробелы.
• Ошибки при ручном вводе.

Способ 1: Формула ПОДСТАВИТЬ (для любых пробелов)

Самый надежный метод, удаляющий как обычные, так и неразрывные пробелы (код 160), которые часто приходят из интернета.

1. В соседней пустой ячейке введите формулу: =ЗНАЧЕН(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;" ";"");СИМВОЛ(160);""))
   • Внутренний ПОДСТАВИТЬ убирает обычные пробелы.
   • Внешний ПОДСТАВИТЬ удаляет неразрывные пробелы.
   • ЗНАЧЕН преобразует итоговый текст в число.
2. Протяните формулу вниз до конца списка.
3. Выделите полученные значения, нажмите Ctrl+C, затем кликните правой кнопкой мыши на исходный столбец и выберите Специальная вставка → Значения.
4. Удалите вспомогательный столбец.

Способ 2: Комбинация ТРИМ + ПЕЧСИМВ + ЗНАЧЕН

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

Формула: =ЗНАЧЕН(ПЕЧСИМВ(ТРИМ(ПОДСТАВИТЬ(A1;" ";" "))))

• ТРИМ: Удаляет лишние пробелы по краям и оставляет только один между словами (если это нужно).
• ПЕЧСИМВ: Удаляет все непечатаемые знаки.
• ЗНАЧЕН: Конвертирует чистый текст в число.

После применения формулы обязательно выполните «Специальную вставку» значений, чтобы зафиксировать результат.

Способ 3: Инструмент «Текст по столбцам» (без формул)

Самый быстрый способ для больших массивов данных, если пробелы только обычные.

1. Выделите столбец с проблемными данными.
2. Перейдите на вкладку Данные → Текст по столбцам.
3. В мастере сразу нажмите Готово (настройки менять не нужно).
4. Если числа остались текстом, повторите действие, но на 3-м шаге мастера выберите формат Общий или Числовой.

Способ 4: Массовое исправление через умножение на 1

Хитрый прием, который заставляет Excel пересчитать текст как число.

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

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

Сравнение методов очистки

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

• #ЗНАЧ! после формулы: Проверьте десятичный разделитель. В русской версии Excel дробная часть отделяется запятой (12,5), а не точкой. Если в данных точка, замените её запятой через ПОДСТАВИТЬ перед функцией ЗНАЧЕН.
• Пробелы не убираются: Скорее всего, это неразрывные пробелы (Alt+0160). Используйте формулу с СИМВОЛ(160) из первого способа.
• Зеленые треугольники не пропадают: После вставки значений выделите диапазон, нажмите на значок предупреждения и выберите «Преобразовать в число».

FAQ

Как убрать пробелы во всем файле сразу? Выделите весь лист (треугольник в левом верхнем углу), скопируйте цифру 1 и используйте «Специальную вставку» → «Умножить» для всех ячеек сразу. Либо используйте Поиск и замену (Ctrl+H): найти пробел, заменить на пусто, но это опасно, если пробелы есть в текстовых заголовках.

Почему сумма не считается, хотя визуально пробелов нет? Вероятно, в ячейках остался невидимый символ (например, апостроф ' перед числом или неразрывный пробел). Используйте функцию ПЕЧСИМВ или проверьте тип данных через =ЕЧИСЛО(A1). Если результат ЛОЖЬ — число все еще текстовое.

Можно ли автоматизировать процесс? Да, для регулярных задач настройте запрос в Power Query («Данные» → «Из таблицы»). Там можно задать шаг «Заменить значение» (пробел на пусто) и «Изменить тип» на десятичное число. При обновлении файла очистка произойдет автоматически.