Как удалить пробелы в числах в Excel, выгруженных из 1С

При выгрузке отчётов и табличных документов из 1С в Excel числа часто содержат неразрывные пробелы — разделители разрядов, которые 1С подставляет автоматически (например, «1 250 000,50»). Для Excel такие значения перестают быть числами: они воспринимаются как текст, не суммируются, не участвуют в формулах и ломают сводные таблицы.

Проблема усугубляется тем, что разделитель в 1С — это не обычный пробел (код 32), а неразрывный пробел (код 160). Поэтому стандартная замена обычного пробела часто не срабатывает, и пользователь видит, что «ничего не поменялось». Ниже разобраны все рабочие способы: от быстрой замены до формул и исправления на стороне 1С.

Почему в числах из 1С появляются пробелы

1С при выводе чисел в табличный документ применяет группировку разрядов: тысячи отделяются неразрывным пробелом. Это сделано для читаемости печатных форм и отчётов. При сохранении табличного документа в формат Excel этот символ переносится вместе со значением.

Неразрывный пробел (символ с кодом 160, в HTML известен как  ) визуально неотличим от обычного пробела (код 32). Именно поэтому замена обычного пробела не даёт результата, а функция СЖПРОБЕЛЫ его не удаляет — она работает только с обычными пробелами.

Есть и вторая причина: иногда 1С выгружает число уже как текст — например, если в ячейке табличного документа значение хранится строкой. В этом случае даже после удаления пробелов Excel продолжит считать содержимое текстом, и потребуется дополнительное преобразование.

Как определить, что мешает считать число числом

Перед очисткой полезно понять, с чем вы имеете дело. Признаки текстового числа: значение прижато к левому краю ячейки, в левом верхнем углу ячейки зелёный треугольник с предупреждением, функция СУММ по диапазону возвращает ноль или игнорирует часть значений.

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

Способ 1. Найти и заменить (Ctrl+H)

Самый быстрый способ для разовой обработки. Выделите диапазон с числами (или весь лист), нажмите Ctrl+H. В поле «Найти» вставьте неразрывный пробел. Есть три способа его ввести:

• скопировать символ прямо из ячейки с числом (выделите пробел между разрядами в строке формул и нажмите Ctrl+C, затем вставьте в поле «Найти»);
• удерживая Alt, набрать на цифровой клавиатуре 0160;
• скопировать символ из таблицы символов Windows (charmap).

Поле «Заменить на» оставьте пустым и нажмите «Заменить все». Excel покажет количество выполненных замен. Если замен ноль — значит, в поле поиска обычный пробел, повторите ввод символа.

1. 1
   Выделите диапазон ячеек с числами из 1С
2. 2
   Нажмите Ctrl+H
3. 3
   Скопируйте неразрывный пробел из ячейки и вставьте в поле «Найти»
4. 4
   Поле «Заменить на» оставьте пустым
5. 5
   Нажмите «Заменить все» и проверьте количество замен
6. 6
   Если числа остались текстом — преобразуйте их в числа

Важно: после замены Excel не всегда автоматически меняет тип значения. Если числа выгружены как текст, они останутся текстом — просто без пробелов. Признаки и лечение описаны в разделе про преобразование.

Способ 2. Формулы ПОДСТАВИТЬ и СЖПРОБЕЛЫ

Когда данные обновляются регулярно (например, ежедневная выгрузка из 1С), удобнее построить формулу, которая очищает значения автоматически. Базовый вариант:

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

Функция СИМВОЛ(160) возвращает неразрывный пробел, ПОДСТАВИТЬ заменяет все его вхождения на пустую строку. Если в данных могут встречаться и обычные пробелы, и пробелы по краям, используйте комбинацию:

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

Чтобы сразу получить число, а не текст, оберните выражение в функцию ЗНАЧЕН:

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

Учтите нюанс: ЗНАЧЕН ориентируется на региональные настройки. Если в выгрузке десятичный разделитель — запятая, а в Windows настроена точка (или наоборот), формула вернёт ошибку #ЗНАЧ!. В этом случае сначала замените разделитель: =ЗНАЧЕН(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;СИМВОЛ(160);"");",";".")).

Способ 3. Преобразование текста в число после очистки

Если пробелы убраны, но Excel по-прежнему считает значения текстом, есть несколько способов завершить преобразование:

• Зелёный треугольник. Выделите диапазон, нажмите на значок предупреждения и выберите «Преобразовать в число». Работает, только если Excel сам распознал проблему.
• Умножение на 1. В пустую ячейку введите 1, скопируйте её, выделите диапазон с числами, вызовите «Специальную вставку» (Ctrl+Alt+V) и отметьте операцию «Умножить». Текстовые числа превратятся в настоящие.
• Текст по столбцам. Выделите столбец, откройте «Данные → Текст по столбцам», нажмите «Готово», не меняя настроек. Excel заново распарсит значения и присвоит числовой формат.

Способ с умножением удобен тем, что не зависит от предупреждений Excel и работает на любых объёмах. Способ «Текст по столбцам» обрабатывает только один столбец за раз, но надёжно сбрасывает текстовый формат ячеек.

Способ 4. Исправить выгрузку на стороне 1С

Если выгрузка делается регулярно и есть доступ к конфигурации, правильнее убрать пробелы на этапе формирования файла. Тогда Excel получит уже чистые числа, и ручная обработка не понадобится.

В типовых отчётах на СКД формат числа задаётся в настройках: на вкладке «Наборы данных» или в пользовательских настройках отчёта для поля можно указать формат без группировки разрядов — например, ЧДЦ=2; ЧГ=0. Параметр ЧГ=0 отключает разделение групп разрядов, и неразрывные пробелы не выводятся.

В программно формируемых табличных документах разработчик задаёт формат ячейки через свойство «Формат» или использует функцию Формат() с параметром ЧГ=0. Альтернатива — выгрузка через COM-объект Excel или в формате CSV, где значения передаются без форматирования.

Формат(СуммаДокумента, "ЧДЦ=2; ЧГ=0") // число без разделителей разрядов

Если конфигурация типовая и изменять её нельзя, остаются внешние обработки или регламентная очистка в Excel — например, макрос, который проходит по используемому диапазону и заменяет Chr(160) на пустую строку.

Сравнение способов и типичные ошибки

Выбор способа зависит от частоты задачи и доступа к настройкам 1С. Разовая выгрузка проще всего чистится через Ctrl+H, регулярная — формулами или макросом, а при доступе к конфигурации лучше исправить источник.

Контроль результата и повторяющиеся выгрузки

После очистки сверьте итоги с источником: сравните сумму по столбцу в Excel с контрольной суммой отчёта в 1С. Совпадение итогов — главный признак того, что ни одно значение не потеряно и не искажено при замене.

Если выгрузка повторяется регулярно, автоматизируйте очистку: сохраните книгу с формулами-обработчиками, куда вставляется новая выгрузка, либо запишите макрос. Простейший макрос для очистки выделенного диапазона заменяет Chr(160) и Chr(32) на пустую строку и присваивает ячейкам числовой формат.

Частые вопросы