Убираем лишние пробелы и превращаем текст в числа в Excel

Чтобы убрать пробелы между цифрами и лишние пробелы в ячейках Excel, используйте формулу =ПОДСТАВИТЬ(A2;" ";"") для полного удаления всех пробелов или =СЖПРОБЕЛЫ(A2) для удаления только крайних и двойных пробелов. Если данные не считаются числами из-за скрытых символов, замените неразрывный пробел формулой =ПОДСТАВИТЬ(A2;СИМВОЛ(160);""). После очистки преобразуйте результат в числовой формат через «Специальную вставку» или умножение на 1.

Ниже приведены подробные инструкции для разных ситуаций: от простой очистки текста до работы с «битыми» данными после импорта из 1С или веб-сайтов.

Почему пробелы мешают работе в Excel

Пробелы часто попадают в ячейки при копировании данных из интернета, банковских выписок или систем учета (например, 1С). Это приводит к трем основным проблемам:

1. Ошибки в расчетах. Функции СУММ, СРЗНАЧ игнорируют ячейки, которые выглядят как числа, но содержат текстовые пробелы.
2. Сбои в ВПР (VLOOKUP). Поиск не находит совпадения, если в одном случае «123», а в другом «123 » (с пробелом в конце).
3. Некорректная сортировка и фильтрация. Числа воспринимаются как текст и сортируются в алфавитном порядке.

Базовые функции для очистки текста

В арсенале Excel есть две главные функции для работы с пробелами. Выбор зависит от вашей цели.

1. Функция СЖПРОБЕЛЫ (TRIM)

Удаляет все пробелы из текста, кроме одиночных пробелов между словами. Идеально подходит для очистки имен, адресов и комментариев.

• Что делает: Удаляет пробелы в начале и конце строки, заменяет множественные пробелы между словами на один.
• Формула: =СЖПРОБЕЛЫ(A2)
• Результат: " Иван Петров " → "Иван Петров"

2. Функция ПОДСТАВИТЬ (SUBSTITUTE)

Заменяет один символ на другой. Используется, когда нужно удалить все пробелы без исключения, например, в номерах телефонов или счетах.

• Что делает: Находит каждый пробел и заменяет его на пустоту.
• Формула: =ПОДСТАВИТЬ(A2;" ";"")
• Результат: "1 234 567" → "1234567"

Проблема неразрывных пробелов (Символ 160)

Частая ситуация: вы применяете СЖПРОБЕЛЫ или ПОДСТАВИТЬ, но пробелы не исчезают, а число не становится числом. Это неразрывный пробел (код символа 160), который часто приходит из веба или 1С. Обычный пробел имеет код 32, и стандартные функции его не видят как обычный знак.

Решение: Используйте функцию СИМВОЛ (или CHAR в английской версии) для указания кода 160.

1. Формула для замены:

    =ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ")
    ```
    Эта формула заменит неразрывный пробел на обычный. После этого можно применить `СЖПРОБЕЛЫ`.

2.  **Полная очистка (Универсальная формула):**
    Чтобы сразу получить чистое число, объедините замены:
    
```excel
    =--ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;СИМВОЛ(160);" ");" ";"")
    ```
    *Двойной минус (`--`) в начале формулы принудительно преобразует итоговый текст в число.*

## Преобразование текста в число после очистки

После удаления пробелов ячейка может остаться в формате «Текст» (выравнивание по левому краю, зеленый треугольник в углу). Чтобы запустить расчеты, нужно конвертировать данные.

### Способ 1: Математическая операция (Быстрый)
Умножьте очищенный диапазон на 1.
1.  В любой пустой ячейке напишите цифру `1`.
2.  Скопируйте эту ячейку (`Ctrl+C`).
3.  Выделите диапазон с «текстовыми» числами.
4.  Нажмите правой кнопкой мыши → **Специальная вставка** → выберите **Умножить** → **ОК**.
Excel пересчитает значения, превратив их в настоящие числа.

### Способ 2: Текста по столбцам (Массовый)
Этот метод не требует формул и работает мгновенно для целых колонок.
1.  Выделите столбец с данными.
2.  Перейдите на вкладку **Данные** → **Текст по столбцам**.
3.  В мастере просто нажмите **Готово** (настройки менять не нужно).
Это сбросит текстовый формат и применит общий числовой.

## Практические примеры формул

<div class="table-container"><table style="border-collapse: collapse; width: 100%; margin: 16px 0;"><thead><tr><th style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; background: #f9fafb; font-weight: 600;">Задача</th><th style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; background: #f9fafb; font-weight: 600;">Исходные данные (Ячейка A1)</th><th style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; background: #f9fafb; font-weight: 600;">Формула</th><th style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; background: #f9fafb; font-weight: 600;">Результат</th></tr></thead><tbody><tr><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;">Убрать пробелы по краям</td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">&quot;  100  &quot;</code></td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">=СЖПРОБЕЛЫ(A1)</code></td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">100</code> (текст)</td></tr><tr><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;">Удалить все пробелы</td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">&quot;1 000 000&quot;</code></td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">=ПОДСТАВИТЬ(A1;&quot; &quot;;&quot;&quot;)</code></td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">1000000</code> (текст)</td></tr><tr><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;">Превратить в число</td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">&quot; 1 234 &quot;</code></td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">=--ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);&quot; &quot;;&quot;&quot;)</code></td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">1234</code> (число)</td></tr><tr><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;">Убрать неразрывный пробел</td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">&quot;500&quot;</code> (с кодом 160)</td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">=ПОДСТАВИТЬ(A1;СИМВОЛ(160);&quot;&quot;)</code></td><td style="border: 1px solid #e5e7eb; padding: 8px; text-align: left; vertical-align: top;"><code style="background-color: rgba(0,0,0,0.05); padding: 2px 4px; border-radius: 3px; font-family: monospace; font-size: 0.9em;">500</code></td></tr></tbody></table></div>


## Частые ошибки

*   **Игнорирование региональных настроек.** Если вы удаляете пробелы в десятичных дробях (например, «12, 5»), убедитесь, что не удалили нужный разделитель. В русской локали разделитель — запятая, пробел обычно используется только для группировки тысяч.
*   **Ошибки #ЗНАЧ!** Возникают, если в ячейке помимо цифр и пробелов есть буквы или другие символы (например, «100 руб.»). Перед очисткой удалите текст функцией `ПОДСТАВИТЬ` или используйте надстройку для извлечения только цифр.
*   **Формула не обновляется.** Если вы заменили формулы на значения, но формат остался текстовым, математические операции все равно не будут работать. Обязательно выполните конвертацию в число (умножением на 1 или через «Текст по столбцам»).

## FAQ

**Как убрать пробелы во всем столбце сразу без формул?**
Выделите столбец, нажмите `Ctrl+H` (Найти и заменить). В поле «Найти» поставьте пробел (нажмите пробел), поле «Заменить на» оставьте пустым. Нажмите «Заменить все». *Внимание: это удалит вообще все пробелы, включая те, что нужны в тексте.*

**Почему функция СЖПРОБЕЛЫ не убирает пробелы?**
Скорее всего, это неразрывные пробелы (из 1С или браузера). Используйте формулу с `СИМВОЛ(160)`, описанную выше.

**Можно ли сделать это макросом?**
Да, но для разовой задачи формулы и «Текст по столбцам» работают быстрее. Макрос имеет смысл, если вы чистите данные ежедневно в одном и том же отчете.