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

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

Типичная история: бухгалтер выгрузил оборотно-сальдовую ведомость в Excel, поставил внизу СУММ и получил ноль. Цифры прижаты к левому краю, в углу ячеек зелёные треугольники. Excel считает «1 254 300,00» текстом, потому что внутри стоят пробелы и, иногда, не тот разделитель дробной части.

Почему Excel принимает суммы из 1С за текст

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

Как это выглядит на практике:

• 📌 значения выровнены по левому краю, хотя формат ячейки «Общий»;
• 📌 СУММ по столбцу даёт 0, а СЧЁТ — тоже 0;
• 📌 зелёный треугольник и подсказка «Число сохранено как текст»;
• 📌 сортировка идёт «как по алфавиту»: 1 000 оказывается раньше 200;
• 📌 сводная таблица показывает «Количество», а не «Сумму».

Откуда берётся текст вместо чисел. Чаще всего — из копирования отчёта через буфер обмена (Ctrl+C в 1С → Ctrl+V в Excel): переносится отформатированное представление. Второй источник — печатные формы. В счёте, акте или ТОРГ-12 суммы часто выводятся в макет уже как готовая строка, и даже при сохранении в .xlsx ячейка остаётся текстовой.

Третья причина — разделитель дробной части. 1С пишет запятую. Если в Windows или в Excel задана точка, то «1254300,00» без пробелов всё равно останется текстом.

Как проверить, какой символ мешает

Прежде чем чистить, убедитесь, что это именно неразрывный пробел. Пусть «1 254 300,00» лежит в A2, тогда второй символ — разделитель:

=КОДСИМВ(ПСТР(A2;2;1))

Результат 160 — неразрывный пробел, 32 — обычный. Если получилось 63 или формула выдаёт что-то странное, проверьте через =ЮНИКОД(ПСТР(A2;2;1)): код 8239 означает узкий неразрывный пробел (U+202F), он встречается при выгрузке через другие программы и при некоторых региональных настройках.

Дополнительная проверка — =ДЛСТР(A2). Если длина больше видимого числа символов, в ячейке есть невидимые хвосты: пробел в конце, перенос строки, табуляция.

Способ 1. Замена через «Найти и заменить» за минуту

Подходит, когда нужно быстро привести в порядок один отчёт.

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

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

После замены числа обычно сами прыгают вправо. Если остались слева — значит, Excel не принимает разделитель дроби, об этом в разделе про форматы ниже.

Если числа не ожили после замены

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

• 🔹 выделите диапазон, нажмите на значок с восклицательным знаком рядом и выберите «Преобразовать в число»;
• 🔹 Данные → Текст по столбцам, сразу нажмите «Готово» — Excel перечитает каждую ячейку;
• 🔹 в пустой ячейке напишите 1, скопируйте её, выделите суммы, Главная → Вставить → Специальная вставка, отметьте «умножить» и нажмите ОК.

Неразрывные пробелы и прочие невидимые символы

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

Что встречается в выгрузках из 1С и чем это убирать:

ПЕЧСИМВ полезна в связке, но тоже не трогает символ 160 — он считается печатаемым.

Способ 2. Формулы: очистка в соседнем столбце

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

Базовая формула для числа в A2:

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

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

Универсальный вариант, который снимает и обычные пробелы, и переносы строк:

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

ЧЗНАЧ: когда разделители не совпадают с настройками

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

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

Это лучший выбор, если файл открывают коллеги с английским Excel: формула даст одинаковый результат у всех. Если в одной выгрузке встречаются и точки, и запятые как дробный разделитель, сначала приведите их к одному виду через ПОДСТАВИТЬ.

Как избавиться от формул после очистки

Столбец с формулами зависит от исходного. Если исходник удалить, получите #ССЫЛКА!. Поэтому:

1. Выделите столбец с результатом и нажмите Ctrl+C.
2. Правой кнопкой по тому же месту → «Параметры вставки» → «Значения».
3. Теперь исходный текстовый столбец можно удалить.

Способ 3. Power Query и макрос для регулярных выгрузок

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

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

Power Query встроен в Excel 2016 и новее (в 2010–2013 ставится отдельной надстройкой).

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

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

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

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

Имена столбцов "Сумма" и "Остаток" замените на свои, Источник — на имя предыдущего шага.

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

Макрос работает прямо по месту, без вспомогательных столбцов. Откройте редактор Alt+F11, выберите Insert → Module и вставьте код:

Sub CleanNumbersFrom1C()
    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 = c.Value
            s = Replace(s, 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, выберите CleanNumbersFrom1C и «Выполнить».

Что делает код: берёт только текстовые ячейки без формул, вырезает три вида пробелов, проверяет, что осталось число, и записывает его как число с форматом «1 234,56». Ячейки с текстом вроде «Итого» не трогает.

IsNumeric и CDbl читают дробный разделитель по настройкам Windows. При русских настройках «1254300,00» распознается правильно. Если на компьютере в качестве дробного разделителя задана точка, добавьте строку s = Replace(s, ",", ".") перед проверкой.

Какой способ выбрать

Формат ячеек и разделители: чтобы проблема не вернулась

Пробелы убраны, но иногда числа так и не становятся числами. Тогда виноваты настройки разделителей.

Проверьте в Excel: Файл → Параметры → Дополнительно, блок «Параметры правки». Если снят флажок «Использовать системные разделители», посмотрите, что указано в полях «Разделитель целой и дробной части» и «Разделитель разрядов». Для выгрузок из 1С нужна запятая как дробный разделитель.

Если флажок стоит, Excel берёт разделители из Windows: Панель управления → Региональные стандарты → Дополнительные параметры.

Не меняйте эти настройки ради одного файла на общем компьютере — поменяется поведение всех таблиц у пользователя. Для разового случая надёжнее ЧЗНАЧ или Power Query с локалью.

Как вернуть красивый вид без пробелов-символов

Разделение на разряды можно получить форматом, а не символами в тексте. Выделите суммы, Ctrl+1 → «Числовой», отметьте «Разделитель групп разрядов», число знаков после запятой — 2. Внешне получится то же «1 254 300,00», но ячейка будет числом и войдёт в СУММ.

Что не трогать при очистке

Не прогоняйте замену и макрос по всему листу. Там, где нули в начале важны, превращение текста в число их съест:

• 🚫 ИНН, КПП, ОГРН, БИК, номера счетов;
• 🚫 артикулы и коды номенклатуры вида «00012»;
• 🚫 номера документов с ведущими нулями;
• 🚫 табельные номера.

Такие столбцы держите в формате «Текстовый».

Как получать из 1С числа, а не текст

Часть работы можно убрать ещё на стороне 1С.

1. Сохраняйте отчёт файлом, а не копируйте. В сформированном отчёте используйте кнопку сохранения (значок дискеты в командной панели) или Ещё → Сохранить как и выберите формат «Лист Excel 2007-…» (.xlsx). Числовые поля отчёта в этом случае обычно приходят числами.
2. Не берите данные из печатных форм. Суммы в счёте или акте часто уже строки. Нужен реестр — сформируйте отчёт (ведомость, анализ счёта, универсальный отчёт) по тем же документам.
3. Копируйте через «Специальную вставку». Если без буфера обмена не обойтись, вставляйте в Excel через Ctrl+Alt+V и пробуйте вариант «Текст» или «Текст в кодировке Юникод» — затем очистка проще, без оформления.
4. Сделайте шаблон. Один раз настройте книгу с Power Query, куда вставляется сырая выгрузка, — и очистка сводится к «Обновить все».

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