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

При выгрузке отчётов, прайсов и табличных документов из 1С в Excel числа часто попадают в ячейки с пробелами-разделителями разрядов: «1 250 000» вместо «1250000». Из-за этого Excel воспринимает значение как текст, формулы СУММ и ВПР возвращают ошибки или нули, а сводные таблицы не строятся. Проблема усугубляется тем, что 1С обычно вставляет не обычный пробел, а неразрывный (код символа 160), который стандартная замена «пробел на пустоту» не находит.

Задача решается несколькими способами: через «Найти и заменить» с правильным символом, формулами ПОДСТАВИТЬ и ЗНАЧЕН, настройкой формата выгрузки на стороне 1С или макросом для регулярных обработок. Выбор зависит от того, разовая это операция или ежедневная рутина, и есть ли доступ к изменению настроек в 1С.

Ниже разобраны все рабочие методы с точными действиями, признаками результата и типичными ошибками, из-за которых замена «не срабатывает».

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

1С форматирует числа по правилам, заданным в конфигурации или региональных настройках: группы разрядов разделяются для читаемости («12 345,67»). При сохранении табличного документа в Excel эта строка форматирования переносится как есть — вместе с разделителем групп разрядов.

Ключевой нюанс: в качестве разделителя 1С обычно использует неразрывный пробел (символ с кодом 160, в Unicode — U+00A0), а не обычный пробел (код 32). Визуально они неотличимы, но для Excel это разные символы. Поэтому, когда пользователь вводит в окне замены обычный пробел с клавиатуры, Excel честно сообщает «не найдено», хотя пробелы на экране явно видны.

Дополнительно разрядность может «приезжать» из региональных настроек Windows, если отчёт формируется через COM-объект Excel или сохраняется в CSV. В этом случае разделитель берётся из системных настроек и тоже может оказаться неразрывным пробелом.

Как определить, какой именно пробел стоит в числе

Прежде чем заменять, полезно убедиться, какой символ мешает. Скопируйте значение из ячейки в строку формул, выделите пробел между цифрами и скопируйте только его (Ctrl+C). Этот символ затем вставляется в поле «Найти» — замена гарантированно сработает, потому что вы ищете именно тот символ, который есть в данных.

Второй способ — формула. В пустой ячейке введите =КОДСИМВ(ПСТР(A2;3;1)), где A2 — проблемная ячейка, а 3 — позиция пробела в числе. Результат 32 означает обычный пробел, 160 — неразрывный. Если число выглядит как «1 250», но формула ЛЕВСИМВ/ПСТР возвращает неожиданные значения, возможно, в ячейке вообще не текст, а число с пользовательским форматом отображения — тогда замена не нужна вовсе.

Способ 1. Найти и заменить неразрывный пробел

Самый быстрый метод для разовой обработки. Выделите диапазон с числами или весь лист, нажмите Ctrl+H. В поле «Найти» вставьте неразрывный пробел одним из двух способов: скопируйте его из ячейки с числом или, удерживая Alt, наберите 0160 на цифровом блоке клавиатуры (NumLock должен быть включён). Поле «Заменить на» оставьте пустым и нажмите «Заменить все».

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

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

Способ 2. Формулы ПОДСТАВИТЬ и ЗНАЧЕН

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

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

После проверки результата формульный столбец можно скопировать и вставить «как значения» поверх исходного, а вспомогательный удалить. Для повторяющихся выгрузок оставьте формулы в отдельном файле-обработчике: вставляете свежую выгрузку на первый лист, на втором сразу получаете чистые числа.

Способ 3. Убрать пробелы на стороне 1С

Если есть доступ к настройкам отчёта или конфигурации, правильнее не чистить данные в Excel, а выгружать их сразу без разделителей. В типовых отчётах на СКД (управляемые формы) формат числа задаётся в настройках: откройте настройки отчёта, найдите оформление нужного поля и в параметре «Формат» уберите разделитель групп — например, укажите форматную строку вида «Ч=0» или снимите флаг разделения разрядов, если он есть.

При сохранении табличного документа из 1С в файл Excel (Файл → Сохранить как → Лист Excel) форматирование чисел сохраняется из табличного документа. Если число в ячейке табличного документа хранится как число, а не как строка, Excel получит корректное числовое значение, и проблема вообще не возникнет. Пробелы появляются в основном тогда, когда разработчик отчёта выводит число как форматированную строку.

Если отчёт дорабатывает программист, решение — выводить в ячейку числовое значение без функции Формат() или задавать форматную строку без разделителя групп разрядов: Формат(Сумма, "ЧГ=0") — параметр ЧГ=0 отключает группировку разрядов. Это самый надёжный вариант для регулярных выгрузок, потому что исключает ручную обработку на каждом файле.

Способ 4. Макрос для регулярной обработки

Когда файлов много и структура у них одинаковая, рутину автоматизируют простым макросом. Откройте редактор VBA (Alt+F11), вставьте модуль и добавьте процедуру, которая проходит по использованному диапазону активного листа, заменяет оба типа пробелов и преобразует значения в числа.

Sub CleanNumbers()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange.Cells
        If VarType(c.Value) = vbString Then
            Dim s As String
            s = Replace(c.Value, Chr(160), "")
            s = Replace(s, " ", "")
            If IsNumeric(s) Then c.Value = CDbl(s)
        End If
    Next c
End Sub

Макрос безопасен для смешанных данных: текстовые ячейки (наименования, артикулы с буквами) он пропустит, потому что после очистки IsNumeric вернёт ложь. Однако осторожность нужна с кодами, которые состоят только из цифр, но должны оставаться текстом — например, ИНН, артикулы с ведущими нулями, номера договоров. Для таких колонок добавьте исключение по номеру столбца.

Проверка результата и типичные ошибки

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

Что выбрать в вашей ситуации

Для большинства бухгалтерских и управленческих задач достаточно связки «Ctrl+H с Alt+0160 + преобразование в число» — она занимает меньше минуты. Вкладываться в макрос или доработку отчёта имеет смысл, когда выгрузка делается ежедневно или её обрабатывают несколько человек.

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