Удаление пробелов в числах в Excel¶
Удаление пробелов в числах в Excel — это операция очистки данных, направленная на устранение лишних символов пробела в ячейках с числовыми значениями, из-за которых Excel распознаёт их как текст и не может использовать в вычислениях. Пробелы чаще всего появляются при импорте данных из внешних источников (1С, базы данных, CSV-файлы, веб-формы) и мешают сортировке, суммированию и построению диаграмм.
¶Причины появления пробелов
Пробелы в числовых ячейках возникают по нескольким причинам:
- Импорт из внешних систем. При выгрузке из 1С, БД или текстовых файлов в полях нередко остаются ведущие или концевые пробелы.
- Ручной ввод. Пользователь случайно нажимает пробел перед или после числа.
- Неразрывные пробелы (nbsp). Символ с кодом 160, скопированный из веб-страниц или PDF, визуально неотличим от обычного пробела, но не удаляется стандартной заменой.
- Разделители разрядов. В некоторых локалях пробел используется как разделитель тысяч (например, «1 000 000»), и при импорте он превращается в обычный текстовый символ.
¶Способы удаления пробелов
¶Функция СЖПРОБЕЛЫ
Самый простой способ — встроенная функция СЖПРОБЕЛЫ (в англоязычной версии — TRIM). Она удаляет все лишние пробелы между словами и обрезает пробелы в начале и конце строки, оставляя один пробел между словами.
В свободной ячейке (например, D1) вводится формула:
`` =СЖПРОБЕЛЫ(A1) ``
Затем результат копируется вниз по столбцу. Для чисел, где пробелы не являются разделителями разрядов, функция полностью решает проблему. Однако СЖПРОБЕЛЫ не удаляет неразрывные пробелы — для них требуется дополнительная обработка.
¶Замена через диалог «Найти и заменить»
Универсальный метод, не требующий формул:
- Выделить нужный столбец.
- Нажать Ctrl + H.
- В поле «Найти» поставить пробел (или вставить неразрывный пробел через Ctrl + Shift + пробел в некоторых версиях Office).
- В поле «Заменить на» ничего не вводить.
- Нажать «Заменить все».
Этот способ удаляет все пробелы в выделенном диапазоне, включая неразрывные, если их вставить в поле поиска. Недостаток — если пробелы являются разделителями разрядов, они исчезнут, и число станет «1000000» вместо «1 000 000».
¶Функция ЗАМЕНИТЬ
Для адресного удаления конкретного символа используется функция ЗАМЕНИТЬ (SUBSTITUTE):
`` =ЗАМЕНИТЬ(A1;" ";"") ``
Она заменяет все вхождения пробела на пустую строку. Вариант с удалением неразрывного пробела:
`` =ЗАМЕНИТЬ(A1;СИМВОЛ(160);"") ``
Комбинированный вариант удаляет оба типа пробелов одновременно:
`` =ЗАМЕНИТЬ(ЗАМЕНИТЬ(A1;СИМВОЛ(160);"");" ";"") ``
¶Преобразование текста в число
После удаления пробелов ячейка всё ещё остаётся текстовой. Чтобы Excel распознал значение как число, применяют один из приёмов:
- Умножить результат на 1:
=(ЗАМЕНИТЬ(A1;" ";""))*1 - Использовать функцию ЧИСЛОЗНАЧ (VALUE):
=ЧИСЛОЗНАЧ(СЖПРОБЕЛЫ(A1)) - Вставить специальную вставку со значением «умножить на 1» через «Специальную вставку».
¶Массовая обработка одного столбца
Чтобы заменить данные в самом столбце без создания вспомогательного:
- Вставить формулу в соседний свободный столбец.
- Выделить результат, скопировать (Ctrl + C).
- Выделить исходный столбец, нажать Ctrl + Shift + V (или Правка → Специальная вставка).
- Выбрать «Значения» и подтвердить.
- Удалить вспомогательный столбец.
Для больших таблиц удобно использовать макрос на VBA:
``vba Sub RemoveSpaces() Dim rng As Range For Each rng In Selection If Not IsEmpty(rng) Then rng.Value = Replace(Replace(rng.Value, Chr(160), ""), " ", "") End If Next rng End Sub ``
Макрос удаляет и обычные, и неразрывные пробелы из выделенного диапазона.
¶Формат ячеек как профилактика
Чтобы Excel сразу воспринимал вводимые числа корректно, ячейки столбца рекомендуется заранее форматировать как «Число» или «Денежный» через правый клик → «Формат ячеек». При импорте данных через «Данные → Получить данные → Из файла» в мастере подключения можно задать очистку пробелов автоматически.
¶Особенности локализации
В русскоязычной версии Excel разделитель аргументов в формулах — точка с запятой (;), а десятичный разделитель — запятая. В англоязычной версии используется запятая (,) как разделитель аргументов и точка как десятичный разделитель. При копировании формул между версиями это может вызывать ошибки.
Источники:
- Поддержка Microsoft — документация по функциям Excel
- «Excel 2019: полный курс профессионала» Дж. Уокера
- «Большая книга Excel» Ч. Уолден