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

Чтобы убрать пробел в числе после выгрузки из 1С, выделите столбец в Excel, нажмите Ctrl+H, в поле «Найти» введите неразрывный пробел (Alt + 0160 на цифровой клавиатуре), поле «Заменить на» оставьте пустым и нажмите «Заменить все». Обычный пробел с клавиатуры здесь не сработает: 1С разделяет разряды символом с кодом 160, а не 32.

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

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

1С показывает суммы с разделителем разрядов: 1 250 000,00. Между группами цифр стоит не обычный пробел, а неразрывный — U+00A0, код 160. Его задача — не дать строке переноситься посреди числа.

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

• 📋 Копирование через буфер. Выделили ячейки отчёта, нажали Ctrl+C, вставили в Excel. В буфер уходит текст в том виде, как он виден на экране, вместе с пробелами.
• 🧩 Нетиповой отчёт или печатная форма. Разработчик вывел сумму в макет через Формат(Сумма, "ЧДЦ=2"). Функция возвращает строку, и в файл она сохраняется строкой.
• 🌐 Посредники. Выгрузка сначала в HTML, CSV или Google Таблицы, потом в Excel. Каждое преобразование может превратить число в текст.

Как понять, что виноват именно неразрывный пробел:

💡 Изредка встречается узкий неразрывный пробел с кодом 8239 (U+202F). Если ЮНИКОД показал именно его, во всех способах ниже подставляйте ЮНИСИМВ(8239) вместо СИМВОЛ(160).

Как убрать пробелы в числах через «Найти и заменить»

Самый быстрый способ для разовой выгрузки. Работает в любой версии Excel.

1. Выделите столбцы с суммами. Если выделить весь лист, пробелы исчезнут и из текстовых полей — названий контрагентов, назначений платежа.
2. Нажмите Ctrl+H, откроется вкладка «Заменить».
3. Поставьте курсор в поле «Найти». Зажмите Alt и наберите 0160 на цифровом блоке клавиатуры (Num Lock должен быть включён). Отпустите Alt — в поле появится невидимый символ.
4. Поле «Заменить на» оставьте пустым.
5. Нажмите «Заменить все». Excel сообщит, сколько замен сделано.

Если цифрового блока нет, как на большинстве ноутбуков, скопируйте сам символ из данных:

1. Щёлкните ячейку с числом и перейдите в строку формул.
2. Выделите мышью один пробел между разрядами, нажмите Ctrl+C, затем Esc.
3. В окне замены поставьте курсор в «Найти» и нажмите Ctrl+V.

Числа всё ещё текст после замены

Пробелы ушли, а числа по-прежнему слева и с треугольником. Excel не пересчитывает тип значения при замене, если у ячейки формат «Текстовый» или значение было вставлено как текст. Варианты:

• ✅ Выделите диапазон, нажмите на значок с восклицательным знаком рядом с выделением и выберите «Преобразовать в число».
• ✅ Данные → Текст по столбцам, сразу «Готово». Excel заново распознает значения в столбце. Работает для одного столбца за раз.
• ✅ Поставьте у столбца формат «Общий» или «Числовой», затем повторите первый или второй вариант. Одна смена формата ячейки текст в число не превращает.

Формулы для очистки чисел: ПОДСТАВИТЬ, ЧЗНАЧ и почему не помогает ПЕЧСИМВ

Формулы удобны, когда выгрузка повторяется: вставили свежие данные в столбец A — в столбце B сразу готовые числа.

Базовый вариант, работает в Excel 2010 и новее:

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

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

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

В Excel 2013 и новее есть функция ЧЗНАЧ. Она сама принимает разделитель целой части и разделитель групп разрядов, поэтому не зависит от региональных настроек компьютера:

=ЧЗНАЧ(A2;",";СИМВОЛ(160))

Второй аргумент — десятичный разделитель в исходном тексте, третий — разделитель разрядов. Формула корректно разберёт 1 250 000,00 даже на компьютере, где Windows ждёт точку.

Многие советуют СЖПРОБЕЛЫ и ПЕЧСИМВ. С числами из 1С они не справляются:

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

💡 Чтобы избавиться от формул, скопируйте столбец B и вставьте на его же место через Ctrl+Alt+V → «Значения». Исходный столбец A после этого можно удалить.

Десятичный разделитель и формат ячеек: когда пробел не единственная беда

1С в русской локали пишет дробную часть через запятую. Если на компьютере в настройках Windows десятичным разделителем стоит точка (так часто бывает на машинах с английской локалью или после настройки под западную бухгалтерию), Excel не распознает 1250,50 как число даже без пробелов.

Проверить и поправить можно двумя способами:

1. Только для Excel. Файл → Параметры → Дополнительно, блок «Параметры правки». Снимите флажок «Использовать системные разделители» и укажите разделитель целой и дробной части , и разделитель разрядов — пробел. Настройка действует на все книги на этом компьютере.
2. Для всей системы. В Windows откройте региональные параметры (в поиске меню «Пуск» — «Регион»), затем дополнительные параметры формата и проверьте десятичный разделитель и разделитель групп разрядов. Изменение затронет и другие программы.

Если менять настройки нельзя (общий компьютер, политика безопасности), используйте ЧЗНАЧ из предыдущего раздела: ей настройки системы не важны.

Что ещё проверить в формате ячеек:

• 🔢 Формат «Текстовый» у столбца, куда вы вставляете данные. В такую ячейку любое значение попадает как текст. Ставьте «Общий» до вставки.
• 📅 Даты из 1С вида 31.03.2025 при неверной локали тоже остаются текстом. Лечится так же: Текст по столбцам, на третьем шаге мастера формат «Дата» с порядком ДМГ.
• ➖ Отрицательные суммы в некоторых отчётах выводятся в скобках или с минусом через пробел. После очистки проверьте пару таких строк вручную.

Power Query: автоматическая очистка выгрузки из 1С

Если одна и та же выгрузка приходит каждый месяц, настройте обработку один раз. Power Query встроен в Excel 2016 и новее (в 2010–2013 ставится отдельной надстройкой).

1. Выделите таблицу с данными и выберите Данные → Из таблицы/диапазона. Откроется редактор Power Query.
2. Выделите столбец с суммами, на вкладке «Преобразование» нажмите «Замена значений».
3. Раскройте «Дополнительные параметры», включите «Заменить с использованием специальных символов».
4. В поле «Значение для поиска» через «Вставить специальный символ» выберите «Неразрывный пробел». Поле замены оставьте пустым. Нажмите «ОК».
5. Щёлкните значок типа в заголовке столбца, выберите «Использование локали…», тип «Десятичное число», язык «Русский (Россия)».
6. Главная → Закрыть и загрузить. Результат ляжет на новый лист.

В следующий раз вставьте свежую выгрузку на место старой и нажмите Данные → Обновить все.

То же самое в виде формулы на языке M, если удобнее править в строке формул редактора:

= Table.TransformColumns(Источник, {{"Сумма", each Number.FromText(Text.Replace(_, "#(00A0)", ""), "ru-RU"), type number}})

Здесь "#(00A0)" — неразрывный пробел, "ru-RU" говорит Power Query, что дробная часть отделена запятой. Вместо Источник подставьте имя предыдущего шага, вместо "Сумма" — заголовок своего столбца.

Макрос VBA: очистка выделенного диапазона одной кнопкой

Макрос подходит, когда выгрузки разные по структуре и настраивать Power Query под каждую лень. Откройте редактор Alt+F11, вставьте модуль (Insert → Module) и скопируйте код:

Sub CleanNumbersFrom1C()
    Dim c As Range, s As String
    For Each c In Intersect(Selection, ActiveSheet.UsedRange).Cells
        If Not c.HasFormula And VarType(c.Value) = vbString Then
            s = Replace(c.Value, Chr(160), "")
            s = Replace(s, ChrW(8239), "")
            s = Replace(s, " ", "")
            If Len(s) > 0 And IsNumeric(s) Then
                c.NumberFormat = "#,##0.00"
                c.Value = CDbl(s)
            End If
        End If
    Next c
End Sub

Как пользоваться: выделите столбцы с суммами и запустите макрос через Alt+F8. Код трогает только текстовые ячейки без формул и только те, что после очистки похожи на число. Названия и комментарии не пострадают.

Действия макроса нельзя отменить через Ctrl+Z. Запускайте его на копии листа или сохраните книгу перед запуском. Чтобы макрос был доступен во всех книгах, сохраните его в личной книге макросов (PERSONAL.XLSB), а сам файл с макросом — в формате .xlsm.

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

Лучший способ чистить данные — не допускать мусора на выходе из 1С.

Сохраняйте файлом, а не копируйте

В отчётах 1С:Бухгалтерии 3.0, ЗУП 3.1 и УТ 11 на панели формы отчёта есть кнопка сохранения (значок дискеты). Выберите тип «Лист Microsoft Excel 2007-…» (.xlsx). При сохранении табличного документа числовые ячейки уходят в Excel числами, и разделитель разрядов остаётся форматом, а не символом в значении.

Для списков документов и справочников: Ещё → Вывести список…, отметьте нужные колонки, затем сохраните полученный табличный документ тем же способом.

Если отчёт нетиповой

Когда даже сохранённый файл даёт текст, проблема в самом отчёте. Это повод для задачи программисту:

• 🛠 Значение выводится в макет строкой через Формат(). Нужно передавать в параметр макета само число, а формат задавать свойствами ячейки.
• 🛠 В отчёте на системе компоновки данных у поля задан формат с группировкой, а в Excel его нужно без пробелов. Строка формата ЧГ=0 отключает группировку разрядов.
• 🛠 Печатная форма собирается из текстовых фрагментов. Для выгрузки в Excel лучше сделать отдельный вариант отчёта.

Сравнение способов, чтобы выбрать свой:

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