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

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

Типичная картина: бухгалтер копирует из оборотно-сальдовой ведомости колонку сумм, вставляет в Excel, а СУММ выдаёт ноль. Значения прижаты к левому краю, в углу зелёные треугольники. Ниже — пять способов это исправить и один способ вообще не сталкиваться с проблемой.

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

1С выводит суммы в отчётах с разделением на разряды: 1 254 300,50. Разделитель — не обычный пробел (код 32), а неразрывный (код 160, в Юникоде U+00A0). Он нужен, чтобы число не разрывалось переносом строки.

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

Что из этого следует на практике:

• 🔢 СУММ, СРЗНАЧ и сводные таблицы такие ячейки пропускают;
• 🔍 ВПР не находит совпадение между числом и «числом-текстом»;
• ✂️ функция СЖПРОБЕЛЫ не помогает: она удаляет только обычные пробелы с кодом 32;
• 🧹 ПЕЧСИМВ тоже бесполезна: она чистит управляющие символы с кодами 0–31, а 160 к ним не относится.

Проверить, какой символ сидит внутри числа, можно формулой. Если ячейка A2 содержит 1 254 300,50, то =КОДСИМВ(ПСТР(A2;2;1)) вернёт код второго символа. Получили 160 — это неразрывный пробел, получили 32 — обычный.

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

Как сохранить отчёт 1С, чтобы числа сразу были числами

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

Порядок в интерфейсе «Такси»:

1. Сформируйте отчёт.
2. Нажмите в командной панели отчёта кнопку «Сохранить» (значок дискеты). Если её не видно, откройте меню Ещё — там тот же пункт.
3. В поле типа файла выберите «Лист Microsoft Excel 2007-…» (*.xlsx).
4. Укажите папку и имя файла, нажмите «Сохранить».

Открываете файл в Excel — колонки сумм уже прижаты вправо и складываются.

Для разработчиков: если вы сами формируете текстовую выгрузку или CSV из 1С, выводите числа без группировки разрядов: Формат(Сумма, "ЧГ=0"). Тогда пробелов в числе не будет вовсе, и проблема не возникнет у получателя.

Замена через «Найти и заменить»: удаляем неразрывный пробел

Это самый быстрый ручной способ, если пробелы уже в таблице.

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

Нет цифрового блока (ноутбук)? Скопируйте пробел прямо из ячейки: зайдите в ячейку через F2, выделите мышью один пробел между цифрами, Ctrl+C, затем вставьте его в поле «Найти» через Ctrl+V.

После замены Excel чаще всего сам распознаёт числа. Если значения так и остались слева, сделайте ещё один проход: в поле «Найти» запятую, в «Заменить на» — запятую. Повторный ввод заставляет Excel перечитать содержимое ячеек.

Формулы, которые убирают пробелы в числах

Формулы удобны, когда исходные данные трогать нельзя, или выгрузка регулярно перезаписывается, а вспомогательный столбец остаётся.

Базовый вариант для ячейки A2:

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

ПОДСТАВИТЬ убирает все неразрывные пробелы, ЗНАЧЕН превращает результат в число.

Если в данных встречаются и обычные пробелы (например, файл прошёл через другую программу), уберите оба символа:

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

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

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

Такая формула даст число и при точке, и при запятой в настройках Windows.

После расчёта замените формулы значениями: выделите столбец, Ctrl+C, затем «Специальная вставка» → «Значения». Исходный столбец можно удалить.

«Текст по столбцам»: числа за один проход без формул

Мастер «Текст по столбцам» обычно делит текст на части, но умеет и перечитывать столбец как числа с заданными разделителями. Работает с одним столбцом за раз.

1. Выделите столбец с суммами.
2. Откройте вкладку Данные → «Текст по столбцам».
3. Шаг 1: выберите «с разделителями», «Далее».
4. Шаг 2: снимите все галочки разделителей, «Далее».
5. Шаг 3: формат данных столбца — «Общий». Нажмите кнопку «Подробнее».
6. В окне дополнительной настройки укажите разделитель целой и дробной части — ,. В поле разделителя разрядов вставьте неразрывный пробел (через Alt+0160 или скопировав его из ячейки).
7. «ОК» → «Готово».

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

Макрос VBA, если выгрузки из 1С приходят каждый день

Если вы чистите такие файлы регулярно, макрос сэкономит десятки одинаковых действий. Он обрабатывает выделенный диапазон: убирает неразрывные и обычные пробелы и записывает число, если строка в него превращается.

Sub УбратьПробелыВЧислах()
    Dim c As Range
    Dim s As String
    For Each c In Selection.Cells
        If Not c.HasFormula And VarType(c.Value) = vbString Then
            s = Replace(c.Value, Chr(160), "")
            s = Replace(s, " ", "")
            If Len(s) > 0 And IsNumeric(s) Then
                c.Value = CDbl(s)
            End If
        End If
    Next c
End Sub

Как подключить:

1. Нажмите Alt+F11 — откроется редактор VBA.
2. В меню Insert → Module, вставьте код.
3. Чтобы макрос был доступен во всех книгах, сохраните его в личной книге макросов (PERSONAL.XLSB) — проще всего записать в неё любой пустой макрос через вкладку «Разработчик», а затем заменить код.
4. Выделите диапазон и запустите макрос через Alt+F8.

Что важно в этом коде:

• ✅ ячейки с формулами и уже числовые значения не трогаются;
• ✅ текстовые ячейки, которые числом не становятся (наименования, коды с буквами), остаются как были;
• ✅ CDbl берёт десятичный разделитель из настроек Windows, поэтому на русской системе 1254300,50 преобразуется правильно.

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

Power Query: настроить очистку один раз

Когда каждый месяц приходит новый файл одной структуры, удобнее Power Query. Шаги очистки запоминаются, а при обновлении применяются заново. Инструмент встроен в Excel 2016 и новее (вкладка Данные).

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

В строке формул Power Query шаг замены выглядит так:

= Table.ReplaceValue(Источник, "#(00A0)", "", Replacer.ReplaceText, {"Сумма"})

Вместо "Сумма" подставьте имя своего столбца. Чтобы обработать новый файл, замените исходные данные и нажмите Данные → «Обновить все».

Выбор локали на шаге 5 решает вопрос с запятой: Power Query прочитает 1254300,50 как число, даже если в системе стоит точка.

Почему число всё ещё не считается: ошибки и проверки

Пробелы убрали, а СУММ по-прежнему ноль? Разберите по пунктам.

• 📄 У столбца формат «Текстовый». Смена формата на «Числовой» не преобразует уже введённые значения. Нужно перевести их заново: «Текст по столбцам» или формула.
• 🔣 Точка вместо запятой. Проверьте десятичный разделитель в Файл → Параметры → Дополнительно, флажок «Использовать системные разделители». Значения должны совпадать с тем, что пришло из 1С.
• ✏️ Апостроф в начале. Некоторые выгрузки ставят ' перед значением, чтобы сохранить текст. Его видно только в строке формул. Помогает «Текст по столбцам».
• 🧩 В ячейке остался другой невидимый символ. Проверьте =ДЛСТР(A2): если длина больше видимого числа знаков, переберите символы через КОДСИМВ(ПСТР(...)) и уберите найденный код через ПОДСТАВИТЬ.
• 📅 Даты превратились в числа или наоборот. Замена по всему листу затрагивает колонки с датами и номерами документов. Работайте только по выделенному столбцу сумм.
• 🔗 ВПР не находит коды. Если ищете по коду номенклатуры, приведите обе стороны к одному типу: либо обе к тексту, либо обе к числу.

Быстрая проверка результата: выделите столбец и посмотрите на строку состояния внизу окна Excel. Если там появились «Сумма» и «Среднее», значения стали числами. Если видно только «Количество» — в столбце ещё текст.

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