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

При выгрузке отчётов и табличных документов из 1С в Excel пользователи регулярно сталкиваются с лишними пробелами: числа не суммируются, поиск по артикулу не находит совпадений, а ВПР возвращает ошибку #Н/Д. Причина почти всегда одна — вместе с данными копируются обычные и неразрывные пробелы, которые 1С использует для форматирования чисел, выравнивания текста и отступов в иерархических списках.

Особенно коварен неразрывный пробел (код символа 160): он выглядит как обычный пробел, но функция СЖПРОБЕЛЫ его не удаляет. Именно им 1С разделяет разряды в числах — например, «1 234 567,89». Excel воспринимает такую запись как текст, и любые вычисления с ней невозможны до очистки.

Задача решается несколькими способами: встроенными средствами 1С ещё до выгрузки, формулами Excel, инструментом «Найти и заменить» и макросами для регулярных операций. Ниже разобраны все варианты с указанием, когда какой подходит, и типичные ошибки, из-за которых очистка не срабатывает.

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

1С форматирует числа в отчётах, разделяя разряды неразрывным пробелом — так требует настройка региональных стандартов, заданная в конфигурации или операционной системе. При копировании через буфер обмена или сохранении табличного документа в формате MXL/XLSX этот символ переносится вместе с цифрами.

Вторая причина — ведущие пробелы в строковых полях. В справочниках (номенклатура, контрагенты) наименования могут содержать пробелы в начале или конце, добавленные при вводе вручную или при загрузке из внешних источников. В иерархических списках 1С дополнительно вставляет отступы пробелами перед подчинёнными элементами.

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

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

Самый надёжный вариант — не дать пробелам попасть в файл. Если отчёт сохраняется через «Файл → Сохранить как» в формате «Лист Excel», проверьте настройку группировки разрядов. В типовых конфигурациях формат чисел задаётся в макете отчёта, и изменить его может только разработчик или администратор с правами на изменение конфигурации.

Для разовой задачи проще скопировать данные не из формы отчёта, а через табличный документ: откройте отчёт, сохраните его как файл MXL или XLSX, затем откройте файл в Excel. При таком пути форматирование переносится аккуратнее, чем при прямом копировании через буфер обмена, хотя неразрывные пробелы в числах всё равно могут сохраниться.

Если выгрузка выполняется обработкой или через «Ещё → Вывести список» в справочнике, выбирайте вывод только видимых колонок и отключайте служебные поля — это уменьшит количество пустых ячеек и объединённых областей, которые потом придётся чистить вручную.

Способ 2. Убрать пробелы инструментом «Найти и заменить»

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

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

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

Ограничение метода: замена работает только с тем символом, который вы точно указали. Если в данных смешаны обычные и неразрывные пробелы, потребуется две замены. Также замена не убирает пробелы по краям строк, если их несколько подряд с разными кодами — здесь надёжнее формулы.

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

Функция СЖПРОБЕЛЫ удаляет лишние обычные пробелы: ведущие, конечные и повторные между словами, оставляя одиночные. Формула =СЖПРОБЕЛЫ(A1) решает задачу для строк из справочников, но не трогает неразрывный пробел.

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

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

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

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

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

Если выгрузки из 1С выполняются ежедневно или еженедельно, ручная замена быстро надоедает. Простой макрос VBA очищает выделенный диапазон от обоих типов пробелов одним запуском.

Откройте редактор VBA (Alt+F11), вставьте модуль и добавьте процедуру, которая перебирает ячейки выделения и заменяет Chr(160) и обычные пробелы. Для чисел дополнительно применяйте CDbl после очистки, предварительно проверив значение функцией IsNumeric.

Sub CleanSpaces()
    Dim c As Range
    For Each c In Selection
        If Not IsEmpty(c) Then
            c.Value = Replace(c.Value, Chr(160), "")
            c.Value = Application.Trim(c.Value)
        End If
    Next c
End Sub

Сохраните файл как книгу с поддержкой макросов (XLSM) или поместите макрос в личную книгу макросов (PERSONAL.XLSB), чтобы он был доступен во всех файлах. Назначьте процедуру на кнопку или сочетание клавиш через «Разработчик → Макросы → Параметры».

Как проверить, что очистка прошла корректно

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

Для строковых данных проверьте длину: =ДЛСТР(A1) до и после очистки покажет, сколько символов удалено. Если ВПР или СВЕДЕНИЯ.ТАБЛИЦА раньше не находили совпадения, повторите поиск на очищенных данных — совпадения должны появиться.

Типичные ошибки и их последствия

Отдельная ловушка — коды и артикулы с ведущими нулями. Если артикул «00123» после очистки и преобразования в число превратился в «123», обратная загрузка в 1С не найдёт соответствия. Для таких полей отключайте автоматическое преобразование: форматируйте столбец как текст до вставки данных или добавляйте апостроф перед значением.

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