Удаление пробелов в числах Excel¶

Удаление пробелов в числах — это набор приёмов работы в табличном процессоре Microsoft Excel, направленных на очистку ячеек от лишних пробельных символов, мешающих корректному распознаванию чисел. Пробелы в ячейке с числом — распространённая проблема при импорте данных из внешних источников (1С, сайты, CSV-файлы, выгрузки из баз данных): Excel воспринимает такую ячейку как текст, из-за чего формулы суммирования, сортировка и математические операции работают некорректно.

¶Причины появления пробелов

Пробелы в числовых ячейках возникают по нескольким причинам:

• Импорт данных — при копировании из веб-страниц или выгрузок из учётных систем в ячейки попадают неразрывные пробелы (символ с кодом 160), которые визуально неотличимы от обычных, но не удаляются стандартными средствами.
• Ручной ввод — пользователь случайно нажимает пробел перед или после числа.
• Форматирование ячеек — в настройках формата числа указано разделение разрядов пробелом, что при экспорте данных превращается в реальные символы.
• Текстовый формат ячейки — Excel хранит число как строку, допуская любые символы.

¶Способы удаления пробелов

¶Функция СЖПРОБЕЛЫ

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

Формула вставляется в соседнюю ячейку:

`` =СЖПРОБЕЛЫ(A1) ``

После вычисления результат копируется и заменяет исходные значения через «Специальная вставка → значения». Функция возвращает текст, поэтому при необходимости число преобразуется обратно умножением на 1 или через форматирование.

¶Функция ЗНАЧ

Для чисто числовых данных подходит функция ЗНАЧ (в англоязычной версии — VALUE), которая преобразует текстовое представление числа в числовое значение, отбрасывая пробелы:

`` =ЗНАЧ(A1) ``

Если в ячейке есть и пробелы, и другие посторонние символы, сначала применяется СЖПРОБЕЛЫ, затем ЗНАЧ:

`` =ЗНАЧ(СЖПРОБЕЛЫ(A1)) ``

¶Поиск и замена

Для массовой очистки используется инструмент «Найти и заменить» (Ctrl + H):

1. В поле «Найти» вводится пробел (просто нажатие пробела на клавиатуре).
2. В поле «Заменить на» ничего не вводится.
3. Нажимается «Заменить всё».

Этот метод удаляет все пробелы в выделенном диапазоне, включая неразрывные. Для удаления именно неразрывных пробелей (код 160) в поле «Найти» вставляется символ через Ctrl + J или сам неразрывный пробел, вставленный из буфера обмена.

¶Мастер замены текста по частям

В русской версии Excel доступна функция ТЕКСТЗАМЕНЬ (в англоязычной версии — SUBSTITUTE), которая заменяет конкретный символ на другой:

`` =ТЕКСТЗАМЕНЬ(A1;" ";"") ``

В отличие от СЖПРОБЕЛЫ, она удаляет все пробелы без остатка, включая неразрывные, если указать нужный символ:

`` =ТЕКСТЗАМЕНЬ(A1;СИМВОЛ(160);"") ``

¶Преобразование данных

Excel включает встроенный инструмент «Текст по столбцам» (вкладка «Данные»), который позволяет преобразовать текстовые ячейки в числовые:

1. Выделить столбец с проблемными данными.
2. Выбрать «Текст по столбцам».
3. На этапе «Разделители» снять все галочки.
4. На этапе «Формат данных столбца» выбрать «Общий» или «Числовой».

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

¶Удаление пробелов в формулах

Если пробелы попадают в вычисления, их можно убрать непосредственно в формуле, не создавая промежуточных столбцов:

`` =СУММ(ЗНАЧ(СЖПРОБЕЛЫ(A1:A100))) ``

Для массивных формул в новых версиях Excel используется динамический массив, в старых — комбинация Ctrl + Shift + Enter.

¶Особенности неразрывных пробелов

Неразрывный пробел (код 160, NBSP) — главная сложность при очистке данных. Он не удаляется функцией СЖПРОБЕЛЫ и не находится стандартной заменой, если не знать о его существовании. Способы работы с ним:

• Вставка символа в поле «Найти» через Ctrl + J.
• Использование ТЕКСТЗАМЕНЬ с СИМВОЛ(160).
• Копирование неразрывного пробела из ячейки и вставка в диалог замены.

¶Практические рекомендации

Для регулярной очистки импортируемых данных рекомендуется создавать шаблон с формулами СЖПРОБЕЛЫ и ЗНАЧ, применяющимися к столбцам сразу после загрузки. При работе с большими массивами данных полезно проверять тип ячеек через формат «Числовой» — если после очистки значения отображаются слева (по умолчанию для текста), пробелы ещё остались.

Источники:

• Microsoft Support — справка по функциям Excel
• «Excel. Полный курс» — С. Немов, издательство «Питер»
• «Microsoft Excel 2019. Полный курс» — Дж. Уокер, издательство «Вильямс»